﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Diagnostics;
using System.Data.SqlClient;
using System.Data;

using PRET.ReportDataDefine.DBData;
using UIData = PRET.ReportDataDefine.UIData;

namespace PRET.PRETService.Data
{
    public class SQLDataProcess : IDisposable
    {
        private readonly static DateTime StartDay = DateTime.Parse("2015/02/01 00:00");

        private readonly static string CREATE_PRET_DB = "CREATE DATABASE [pret] ON  PRIMARY" +
            "( NAME = N'pret', FILENAME = N'C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\pret.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 30MB )" +
            "LOG ON" +
            "( NAME = N'reportsystem_log', FILENAME = N'C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\pret_log.ldf' , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 20%)";

        private readonly static string CREATE_ERPSystem_DB = "CREATE DATABASE [ERPSystem] ON  PRIMARY" +
            "( NAME = N'pret', FILENAME = N'C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\erpSys.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 30MB )" +
            "LOG ON" +
            "( NAME = N'reportsystem_log', FILENAME = N'C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\erpSys_log.ldf' , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 20%)";

        #region 班组工作时间
        private readonly static string CREATE_TEAMWORKTIME_TABLE = "CREATE TABLE [pret].[dbo].[TeamWorkTime](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]," +
            "[TeamID] [varchar](20) NOT NULL," +
            "[MixingerID] [varchar](20) NOT NULL," +
            "[ExtruderID] [varchar](20) NOT NULL," +
            "[PackerID] [varchar](20) NOT NULL," +
            "[QualityID] [varchar](20) NOT NULL," +
            "[ProductionLine] [varchar](20) NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string INSERT_TEAMWORKTIME = "INSERT INTO [pret].[dbo].[TeamWorkTime]" +
           "([StartTime],[EndTime]" +
           ",[TeamID],[MixingerID],[ExtruderID],[PackerID],[QualityID],[ProductionLine],[ProLineCode])" +
        "VALUES" +
           "('{0}'" + // <StartTime, datetime,>
           ",'{1}'" + // <EndTime, datetime,>
           ",'{2}'" + // <TeamID, varchar(20),>
           ",'{3}'" + // <MixingerID, varchar(20),>
           ",'{4}'" + // <ExtruderID, varchar(20),>
           ",'{5}'" + // <PackerID, varchar(20),>
           ",'{6}'" + // <QualityID, varchar(20),>
           ",'{7}'" + // <ProductionLine, varchar(20),>
           ",'{8}')"; // <ProLineCode, varchar(10),>
        private readonly static string UPDATE_TEAMWORKTIME = "UPDATE [pret].[dbo].[TeamWorkTime]" +
            "SET [EndTime] = '{2}'" +
            "WHERE [TeamID] = '{0}' AND [EndTime] = '{1}' AND [ProLineCode] = '{3}'";
        private readonly static string LATEST_TEAMWORKTIME = "SELECT MAX([EndTime]) AS EndTime,[ProLineCode] FROM [pret].[dbo].[TeamWorkTime]" +
            "GROUP BY [ProLineCode]";
        private readonly static string SELECT_TEAMWORKTIME_BY_TEAMID = "SELECT * FROM [pret].[dbo].[TeamWorkTime]" +
            "WHERE [TeamID] = '{0}' AND [ProLineCode] = '{1}'" +
            "ORDER BY [EndTime] DESC";
        private readonly static string SELECT_TEAMWORKTIME_BY_TIME = "SELECT * FROM [pret].[dbo].[TeamWorkTime]" +
            "WHERE [ProLineCode] = '{0}' AND (([StartTime] <= '{1}' AND [EndTime] >= '{1}') OR ([StartTime] >= '{1}' AND [StartTime] <= '{2}'))" +
            "ORDER BY [StartTime] ASC";

        private readonly static string CREATE_PROLINEEFF_TABLE = "CREATE TABLE [pret].[dbo].[ProlineEff](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[Year] [int] NOT NULL DEFAULT ((0))," + 
            "[JanEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[JanHours] [float] NOT NULL DEFAULT ((0))," +
            "[JanWeight] [float] NOT NULL DEFAULT ((0))," +
            "[FebEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[FebHours] [float] NOT NULL DEFAULT ((0))," +
            "[FebWeight] [float] NOT NULL DEFAULT ((0))," +
            "[MarEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[MarHours] [float] NOT NULL DEFAULT ((0))," +
            "[MarWeight] [float] NOT NULL DEFAULT ((0))," +
            "[AprEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[AprHours] [float] NOT NULL DEFAULT ((0))," +
            "[AprWeight] [float] NOT NULL DEFAULT ((0))," +
            "[MayEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[MayHours] [float] NOT NULL DEFAULT ((0))," +
            "[MayWeight] [float] NOT NULL DEFAULT ((0))," +
            "[JunEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[JunHours] [float] NOT NULL DEFAULT ((0))," +
            "[JunWeight] [float] NOT NULL DEFAULT ((0))," +
            "[JulEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[JulHours] [float] NOT NULL DEFAULT ((0))," +
            "[JulWeight] [float] NOT NULL DEFAULT ((0))," +
            "[AugEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[AugHours] [float] NOT NULL DEFAULT ((0))," +
            "[AugWeight] [float] NOT NULL DEFAULT ((0))," +
            "[SepEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[SepHours] [float] NOT NULL DEFAULT ((0))," +
            "[SepWeight] [float] NOT NULL DEFAULT ((0))," +
            "[OctEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[OctHours] [float] NOT NULL DEFAULT ((0))," +
            "[OctWeight] [float] NOT NULL DEFAULT ((0))," +
            "[NovEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[NovHours] [float] NOT NULL DEFAULT ((0))," +
            "[NovWeight] [float] NOT NULL DEFAULT ((0))," +
            "[DecEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[DecHours] [float] NOT NULL DEFAULT ((0))," +
            "[DecWeight] [float] NOT NULL DEFAULT ((0))," +
            "[AvgEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[MaxEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[MinEfficiency] [float] NOT NULL DEFAULT ((0))," +
            "[EfficiencyQuota] [float] NOT NULL DEFAULT ((0))," +
            "[Weight] [float] NOT NULL DEFAULT ((0))," +
            "[Hours] [float] NOT NULL DEFAULT ((0))," +
            "[CurrEfficiency] [float] NOT NULL DEFAULT ((0))" +
            ") ON [PRIMARY]";
        private readonly static string GET_PROLINESTATUS_BY_PROLINE = "SELECT * FROM [ERPSystem].[dbo].[ProLineStatus] " +
            "WHERE [ProductionLine] LIKE '{0}-%' AND [RecordTime] >= '{1}' AND [RecordTime] < '{2}'";
        private readonly static string GET_JCJCURRENT_BY_TIME = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' AND [CurrCurrent] > 10 AND [JsProduce] = 0";
        private readonly static string GET_PROLINEEFF = "SELECT * FROM [pret].[dbo].[ProlineEff] WHERE [ProductionLine] = '{0}' AND [Year] = {1}";
        private readonly static string INSERT_PROLINEEFF = "INSERT INTO [pret].[dbo].[ProlineEff]([ProductionLine],[Year]," +
            "[JanEfficiency],[JanHours],[JanWeight]," +
            "[FebEfficiency],[FebHours],[FebWeight]," +
            "[MarEfficiency],[MarHours],[MarWeight]," +
            "[AprEfficiency],[AprHours],[AprWeight]," +
            "[MayEfficiency],[MayHours],[MayWeight]," +
            "[JunEfficiency],[JunHours],[JunWeight]," +
            "[JulEfficiency],[JulHours],[JulWeight]," +
            "[AugEfficiency],[AugHours],[AugWeight]," +
            "[SepEfficiency],[SepHours],[SepWeight]," +
            "[OctEfficiency],[OctHours],[OctWeight]," +
            "[NovEfficiency],[NovHours],[NovWeight]," +
            "[DecEfficiency],[DecHours],[DecWeight]," +
            "[AvgEfficiency],[MaxEfficiency],[MinEfficiency],[EfficiencyQuota],[Weight],[Hours],[CurrEfficiency]) " +
            "VALUES " +
            "('{0}'," + // <ProductionLine, nvarchar(20),>
            "{1}," + // <Year, int,>
            "{2}," + // <JanEfficiency, float,>
            "{3}," + // <JanHours, float,>
            "{4}," + // <JanWeight, float,>
            "{5}," + // <FebEfficiency, float,>
            "{6}," + // <FebHours, float,>
            "{7}," + // <FebWeight, float,>
            "{8}," + // <MarEfficiency, float,>
            "{9}," + // <MarHours, float,>
            "{10}," + // <MarWeight, float,>
            "{11}," + // <AprEfficiency, float,>
            "{12}," + // <AprHours, float,>
            "{13}," + // <AprWeight, float,>
            "{14}," + // <MayEfficiency, float,>
            "{15}," + // <MayHours, float,>
            "{16}," + // <MayWeight, float,>
            "{17}," + // <JunEfficiency, float,>
            "{18}," + // <JunHours, float,>
            "{19}," + // <JunWeight, float,>
            "{20}," + // <JulEfficiency, float,>
            "{21}," + // <JulHours, float,>
            "{22}," + // <JulWeight, float,>
            "{23}," + // <AugEfficiency, float,>
            "{24}," + // <AugHours, float,>
            "{25}," + // <AugWeight, float,>
            "{26}," + // <SepEfficiency, float,>
            "{27}," + // <SepHours, float,>
            "{28}," + // <SepWeight, float,>
            "{29}," + // <OctEfficiency, float,>
            "{30}," + // <OctHours, float,>
            "{31}," + // <OctWeight, float,>
            "{32}," + // <NovEfficiency, float,>
            "{33}," + // <NovHours, float,>
            "{34}," + // <NovWeight, float,>
            "{35}," + // <DecEfficiency, float,>
            "{36}," + // <DecHours, float,>
            "{37}," + // <DecWeight, float,>
            "{38}," + // <AvgEfficiency, float,>
            "{39}," + // <MaxEfficiency, float,>
            "{40}," + // <MinEfficiency, float,>
            "{41}," + // <EfficiencyQuota, float,>
            "{42}," + // <Weight, float,>
            "{43}," + // <Hours, float,>
            "{44})"; // <CurrEfficiency, float,>)"
        private readonly static string UPDATE_PROLINEEFF = "UPDATE [pret].[dbo].[ProlineEff] " +
            "SET [ProductionLine] = '{0}',[Year] = {1}," +
            "[JanEfficiency] = {2},[JanHours] = {3},[JanWeight] = {4}," +
            "[FebEfficiency] = {5},[FebHours] = {6},[FebWeight] = {7}," +
            "[MarEfficiency] = {8},[MarHours] = {9},[MarWeight] = {10}," +
            "[AprEfficiency] = {11},[AprHours] = {12},[AprWeight] = {13}," +
            "[MayEfficiency] = {14},[MayHours] = {15},[MayWeight] = {16}," +
            "[JunEfficiency] = {17},[JunHours] = {18},[JunWeight] = {19}," +
            "[JulEfficiency] = {20},[JulHours] = {21},[JulWeight] = {22}," +
            "[AugEfficiency] = {23},[AugHours] = {24},[AugWeight] = {25}," +
            "[SepEfficiency] = {26},[SepHours] = {27},[SepWeight] = {28}," +
            "[OctEfficiency] = {29},[OctHours] = {30},[OctWeight] = {31}," +
            "[NovEfficiency] = {32},[NovHours] = {33},[NovWeight] = {34}," +
            "[DecEfficiency] = {35},[DecHours] = {36},[DecWeight] = {37}," +
            "[AvgEfficiency] = {38},[MaxEfficiency] = {39},[MinEfficiency] = {40}," +
            "[EfficiencyQuota] = {41},[Weight] = {42},[Hours] = {43},[CurrEfficiency] = {44} " +
            "WHERE [ProductionLine] = '{0}' AND [Year] = {1}";
        #endregion

        #region 挤出机SQL
        private readonly static string CREATE_JCJINFO_TABLE = "CREATE TABLE [pret].[dbo].[JCJInfo](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[OrderID] [varchar](20) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]," +
            "[UpdateTime] [datetime] NOT NULL" +
            ") ON [PRIMARY]";
        // 表格JCJDetail记录的是过去五分钟内的数据，无论机器是否运行都会记录
        private readonly static string CREATE_JCJDETAIL_TABLE = "CREATE TABLE [pret].[dbo].[JCJDetail](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[RecordTime] [datetime] NOT NULL," +
            "[UpdateTime] [datetime] NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[Bal1Scale] [float] NOT NULL DEFAULT ((0))," +
            "[Bal2Scale] [float] NOT NULL DEFAULT ((0))," +
            "[Bal3Scale] [float] NOT NULL DEFAULT ((0))," +
            "[Bal1RealCount] [float] NOT NULL DEFAULT ((0))," +
            "[Bal2RealCount] [float] NOT NULL DEFAULT ((0))," +
            "[Bal3RealCount] [float] NOT NULL DEFAULT ((0))," +
            "[WLJRevolvingSpeed] [int] NOT NULL DEFAULT ((0))," +
            "[JCJ1TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ2TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ3TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ4TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ5TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ6TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ7TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ8TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ9TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ10TempOffset] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ1SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ2SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ3SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ4SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ5SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ6SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ7SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ8SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ9SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ10SetTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ1RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ2RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ3RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ4RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ5RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ6RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ7RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ8RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ9RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[JCJ10RealTemp] [float] NOT NULL DEFAULT ((0))," +
            "[ExtruderMaxRevolvingSpeed] [int] NOT NULL DEFAULT ((0))," +
            "[ExtruderMinRevolvingSpeed] [int] NOT NULL DEFAULT ((0))," +
            "[ExtruderRevolvingRealSpeed] [int] NOT NULL DEFAULT ((0))," +
            "[ExtruderMaxCurrent] [float] NOT NULL DEFAULT ((0))," +
            "[ExtruderMinCurrent] [float] NOT NULL DEFAULT ((0))," +
            "[ExtruderRealCurrent] [float] NOT NULL DEFAULT ((0))," +
            "[MaxRongZhi] [float] NOT NULL DEFAULT ((0))," +
            "[MinRongZhi] [float] NOT NULL DEFAULT ((0))," +
            "[RealRongZhi] [float] NOT NULL DEFAULT ((0))," +
            "[MaxRemnant] [float] NOT NULL DEFAULT ((0))," +
            "[MinRemnant] [float] NOT NULL DEFAULT ((0))," +
            "[RealRemnant] [float] NOT NULL DEFAULT ((0))," +
            "[ElectricPower] [float] NOT NULL DEFAULT ((0))," +
            "[MaxCurrent] [float] NOT NULL DEFAULT ((0))," +
            "[MinCurrent] [float] NOT NULL DEFAULT ((0))," +
            "[CurrCurrent] [float] NOT NULL DEFAULT ((0))," +
            "[MaxPower] [float] NOT NULL DEFAULT ((0))," +
            "[JsProduce] [int] NOT NULL DEFAULT ((0))" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_INDEX_JCJDETAIL = "CREATE INDEX [IX_JCJDetail] ON [pret].[dbo].[JCJDetail] ([ProductionLine] ASC, [RecordTime] DESC)";
        private readonly static string INSERT_JCJSUMMARYINFO = "INSERT INTO [pret].[dbo].[JCJInfo]" +
           "([ProductionLine],[ProLineCode],[OrderID],[StartTime],[EndTime],[UpdateTime])" +
        "VALUES" +
           "('{0}'" + // <ProductionLine, varchar(20),>
           ",'{1}'" + // <ProLineCode, varchar(20),>
           ",'{2}'" + // <OrderID, varchar(20),>
           ",{3}" + // <StartTime, datetime,>
           ",{4}" + // <EndTime, datetime,>
           ",'{5}')"; // <UpdateTime, datetime>
        private readonly static string INSERT_JCJDETAIL = "INSERT INTO [pret].[dbo].[JCJDetail] " +
            "([RecordTime],[UpdateTime],[ProductionLine],[ProLineCode]" +
            ",[Bal1Scale],[Bal2Scale],[Bal3Scale],[Bal1RealCount],[Bal2RealCount],[Bal3RealCount],[WLJRevolvingSpeed]" +
            ",[JCJ1TempOffset],[JCJ2TempOffset],[JCJ3TempOffset],[JCJ4TempOffset],[JCJ5TempOffset],[JCJ6TempOffset],[JCJ7TempOffset],[JCJ8TempOffset],[JCJ9TempOffset],[JCJ10TempOffset]" +
            ",[JCJ1SetTemp],[JCJ2SetTemp],[JCJ3SetTemp],[JCJ4SetTemp],[JCJ5SetTemp],[JCJ6SetTemp],[JCJ7SetTemp],[JCJ8SetTemp],[JCJ9SetTemp],[JCJ10SetTemp]" +
            ",[JCJ1RealTemp],[JCJ2RealTemp],[JCJ3RealTemp],[JCJ4RealTemp],[JCJ5RealTemp],[JCJ6RealTemp],[JCJ7RealTemp],[JCJ8RealTemp],[JCJ9RealTemp],[JCJ10RealTemp]" +
            ",[ExtruderMaxRevolvingSpeed],[ExtruderMinRevolvingSpeed],[ExtruderRevolvingRealSpeed]" +
            ",[ExtruderMaxCurrent],[ExtruderMinCurrent],[ExtruderRealCurrent]" +
            ",[MaxRongZhi],[MinRongZhi],[RealRongZhi],[MaxRemnant],[MinRemnant],[RealRemnant]" +
            ",[ElectricPower],[MaxCurrent],[MinCurrent],[CurrCurrent],[MaxPower],[JsProduce]) " + 
            "VALUES " +
            "('{0}'" + // <RecordTime, datetime,>
            ",'{1}'" + // <UpdateTime, datetime,>
            ",'{2}'" + // <ProductionLine, nvarchar(20),>
            ",'{3}'" + // <ProLineCode, varchar(10),>
            ",{4}" + // <Bal1Scale, float,>
            ",{5}" + // <Bal2Scale, float,>
            ",{6}" + // <Bal3Scale, float,>
            ",{7}" + // <Bal1RealCount, float,>
            ",{8}" + // <Bal2RealCount, float,>
            ",{9}" + // <Bal3RealCount, float,>
            ",{10}" + // <WLJRevolvingSpeed, int,>
            ",{11}" + // <JCJ1TempOffset, float,>
            ",{12}" + // <JCJ2TempOffset, float,>
            ",{13}" + // <JCJ3TempOffset, float,>
            ",{14}" + // <JCJ4TempOffset, float,>
            ",{15}" + // <JCJ5TempOffset, float,>
            ",{16}" + // <JCJ6TempOffset, float,>
            ",{17}" + // <JCJ7TempOffset, float,>
            ",{18}" + // <JCJ8TempOffset, float,>
            ",{19}" + // <JCJ9TempOffset, float,>
            ",{20}" + // <JCJ10TempOffset, float,>
            ",{21}" + // <JCJ1SetTemp, float,>
            ",{22}" + // <JCJ2SetTemp, float,>
            ",{23}" + // <JCJ3SetTemp, float,>
            ",{24}" + // <JCJ4SetTemp, float,>
            ",{25}" + // <JCJ5SetTemp, float,>
            ",{26}" + // <JCJ6SetTemp, float,>
            ",{27}" + // <JCJ7SetTemp, float,>
            ",{28}" + // <JCJ8SetTemp, float,>
            ",{29}" + // <JCJ9SetTemp, float,>
            ",{30}" + // <JCJ10SetTemp, float,>
            ",{31}" + // <JCJ1RealTemp, float,>
            ",{32}" + // <JCJ2RealTemp, float,>
            ",{33}" + // <JCJ3RealTemp, float,>
            ",{34}" + // <JCJ4RealTemp, float,>
            ",{35}" + // <JCJ5RealTemp, float,>
            ",{36}" + // <JCJ6RealTemp, float,>
            ",{37}" + // <JCJ7RealTemp, float,>
            ",{38}" + // <JCJ8RealTemp, float,>
            ",{39}" + // <JCJ9RealTemp, float,>
            ",{40}" + // <JCJ10RealTemp, float,>
            ",{41}" + // <ExtruderMaxRevolvingSpeed, int,>
            ",{42}" + // <ExtruderMinRevolvingSpeed, int,>
            ",{43}" + // <ExtruderRevolvingRealSpeed, int,>
            ",{44}" + // <ExtruderMaxCurrent, float,>
            ",{45}" + // <ExtruderMinCurrent, float,>
            ",{46}" + // <ExtruderRealCurrent, float,>
            ",{47}" + // <MaxRongZhi, float,>
            ",{48}" + // <MinRongZhi, float,>
            ",{49}" + // <RealRongZhi, float,>
            ",{50}" + // <MaxRemnant, float,>
            ",{51}" + // <MinRemnant, float,>
            ",{52}" + // <RealRemnant, float,>
            ",{53}" + // <ElectricPower, float,>
            ",{54}" + // <MaxCurrent, float,>
            ",{55}" + // <MinCurrent, float,>
            ",{56}" + // <CurrCurrent, float,>
            ",{57}" + // <MaxPower, float,>)";
            ",{58})"; // <JsProduce, float,>)";
        private readonly static string UPDATE_JCJSUMMARY_ENDTIME = "UPDATE [pret].[dbo].[JCJInfo]" +
            "SET [EndTime] = {3}, [UpdateTime] = {4} " +
            "WHERE [ProLineCode] = '{0}' AND [OrderID] LIKE '%{1}' AND [StartTime] = '{2}'";
        private readonly static string LATEST_JCJDETAILTIME = "SELECT MAX([UpdateTime]) AS UpdateTime, [ProLineCode] FROM [pret].[dbo].[JCJDetail] " +
            "GROUP BY [ProLineCode]";
        private readonly static string GET_JCJINFO_BY_ORDERID_WITHOUT_ENDTIME = "SELECT * FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NULL " +
            "ORDER BY [UpdateTime] DESC";
        private readonly static string GET_JCJINFO_BY_ORDERID_WITH_ENDTIME = "SELECT * FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NOT NULL " +
            "ORDER BY [EndTime] DESC";
        private readonly static string GET_JCJINFO = "SELECT * FROM [pret].[dbo].[JCJInfo]" +
            "WHERE [EndTime] IS NOT NULL";
        private readonly static string GET_JCJDETAILINFO_BY_ORDER_TIME = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' " +
            "ORDER BY [RecordTime] DESC";
        #endregion

        #region 均化机SQL
        private readonly static string CREATE_JHJINFO_TABLE = "CREATE TABLE [pret].[dbo].[JHJInfo](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[OrderID] [varchar](20) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]," +
            "[UpdateTime] [datetime] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_JHJDETAIL_TABLE = "CREATE TABLE [pret].[dbo].[JHJDetail](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[RecordTime] [datetime] NOT NULL," +
            "[ElectricPower] [float] DEFAULT ((0))," +
            "[MaxCurrent] [float] DEFAULT ((0))," +
            "[MinCurrent] [float] DEFAULT ((0))," +
            "[MaxPower] [float] DEFAULT ((0))," +
            "[BBoxZDTemper] [float] DEFAULT ((0))," +
            "[ABoxTemper] [float] DEFAULT ((0))," +
            "[PackNumber] [int] DEFAULT ((0))" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_INDEX_JHJDETAIL = "CREATE INDEX [IX_JHJDetail] ON [pret].[dbo].[JHJDetail] ([ProductionLine] ASC, [RecordTime] ASC)";
        private readonly static string CREATE_JHJTRANS_TABLE = "CREATE TABLE [pret].[dbo].[JHJTransRecordTime](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_JHJHEATING_TABLE = "CREATE TABLE [pret].[dbo].[JHJHeatingRecordTime](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[StartTime] [datetime]," +
            "[EndTime] [datetime]" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_JHJPACKING_TABLE = "CREATE TABLE [pret].[dbo].[JHJPackRecordTime](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_JHJTHROW_TABLE = "CREATE TABLE [pret].[dbo].[JHJThrowRecordTime](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[OrderID] [varchar](20) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]," +
            "[Weight] [float] NOT NULL," +
            "[Number] [int] NOT NULL," +
            "[UnitWeight] [float] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string INSERT_JHJDATA = "INSERT INTO [pret].[dbo].[JHJInfo]" +
           "([ProductionLine],[ProLineCode],[OrderID]" +
           ",[StartTime],[EndTime],[UpdateTime]) " +
        "VALUES" +
           "('{0}'" + // <ProductionLine, varchar(20),>
           ",'{1}'" + // <ProLineCode, varchar(10),>
           ",'{2}'" + // <OrderID, varchar(20),>
           ",{3}" + // <StartTime, datetime,>
           ",{4}" + // <EndTime, datetime,>
           ",'{5}')"; // <UpdateTime, datetime>
        private readonly static string INSERT_JHJDETAIL = "INSERT INTO [pret].[dbo].[JHJDetail]" +
           "([ProductionLine],[ProLineCode]" +
           ",[RecordTime],[ElectricPower],[MaxCurrent],[MinCurrent],[MaxPower]" +
           ",[BBoxZDTemper],[ABoxTemper],[PackNumber]) " +
        "VALUES" +
           "('{0}'" + // <ProductionLine, varchar(20),>
           ",'{1}'" + // <ProLineCode, varchar(20)>
           ",'{2}'" + // <RecordTime, datetime,>
           ",{3}" + // <ElectricPower, float>
           ",{4}" + // <MaxCurrent, float>
           ",{5}" + // <MinCurrent, float>
           ",{6}" + // <MaxPower, float>
           ",{7}" + // <BBoxZDTemper, float>
           ",{8}" + // <ABoxTemper, float>
           ",{9})"; // <PackNumber, int>
        private readonly static string INSERT_JHJTRANSDATA = "INSERT INTO [pret].[dbo].[JHJTransRecordTime]" +
           "([ProductionLine],[ProLineCode]" +
           ",[StartTime],[EndTime]) " +
        "VALUES " +
           "('{0}'" + // <ProductionLine, varchar(20),>
           ",'{1}'" + // <ProLineCode, varchar(10),>
           ",{2}" + // <StartTime, datetime,>
           ",{3})"; // <EndTime, datetime,>
        private readonly static string INSERT_JHJHEATINGDATA = "INSERT INTO [pret].[dbo].[JHJHeatingRecordTime]" +
           "([ProductionLine],[ProLineCode]" +
           ",[StartTime],[EndTime]) " +
        "VALUES " +
           "('{0}'" + // <ProductionLine, varchar(20),>
           ",'{1}'" + // <ProLineCode, varchar(10),>
           ",{2}" + // <StartTime, datetime,>
           ",{3})"; // <EndTime, datetime,>
        private readonly static string INSERT_JHJPACKDATA = "INSERT INTO [pret].[dbo].[JHJPackRecordTime]" +
           "([ProductionLine],[ProLineCode]" +
           ",[StartTime],[EndTime])" +
        "VALUES" +
           "('{0}'" + // <ProductionLine, varchar(20),>
           ",'{1}'" + // <ProLineCode, varchar(10),>
           ",{2}" + // <StartTime, datetime,>
           ",{3})"; // <EndTime, datetime,>
        private readonly static string UPDATE_JHJENDTIME = "UPDATE [pret].[dbo].[JHJInfo] " +
            "SET [EndTime] = {3}, [UpdateTime] = {4} " +
            "WHERE [ProductionLine] = '{0}' AND [OrderID] LIKE '%{1}' AND [StartTime] = '{2}'";
        private readonly static string UPDATE_JHJTRANSENDTIME = "UPDATE [pret].[dbo].[JHJTransRecordTime] " +
            "SET [EndTime] = '{1}' " +
            "WHERE [ProductionLine] = '{0}' AND [StartTime] = '{2}'";
        private readonly static string UPDATE_JHJHEATINGENDTIME = "UPDATE [pret].[dbo].[JHJHeatingRecordTime]" +
            "SET [EndTime] = '{1}' " +
            "WHERE [ProductionLine] = '{0}' AND [StartTime] = '{2}'";
        private readonly static string UPDATE_JHJPACKENDTIME_WITH_ENDTIME = "UPDATE [pret].[dbo].[JHJPackRecordTime]" +
            "SET [EndTime] = '{1}' " +
            "WHERE [ProductionLine] = '{0}' AND [StartTime] = '{2}'";
        // 删除指定的单次包装数据
        private readonly static string GET_JHJINFO_BY_ORDERID_WITHOUT_ENDTIME = "SELECT * FROM [pret].[dbo].[JHJInfo]" +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NULL " +
            "ORDER BY [UpdateTime] DESC";
        private readonly static string GET_JHJINFO_BY_ORDERID_WITH_ENDTIME = "SELECT * FROM [pret].[dbo].[JHJInfo] " +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NOT NULL " +
            "ORDER BY [EndTime] DESC";
        private readonly static string GET_JHJTHROWINFO = "SELECT * FROM [pret].[dbo].[JHJThrowRecordTime] " +
            "WHERE [OrderID] LIKE '%{0}' AND [StartTime] >= '{1}' AND [EndTime] <= '{2}'";
        private readonly static string GET_JHJTRANS_BY_ORDERID_NOENDTIME = "SELECT * FROM [pret].[dbo].[JHJTransRecordTime] " +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NULL " +
            "ORDER BY [StartTime] DESC";
        private readonly static string GET_JHJHEATING_BY_ORDERID_NOENDTIME = "SELECT * FROM [pret].[dbo].[JHJHeatingRecordTime] " +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NULL " +
            "ORDER BY [StartTime] DESC";
        private readonly static string GET_JHJPACK_BY_ORDERID_NOENDTIME = "SELECT * FROM [pret].[dbo].[JHJPackRecordTime] " +
            "WHERE [ProLineCode] = '{0}' AND [EndTime] IS NULL " +
            "ORDER BY [StartTime] DESC";

        // 获得最近一次均化机的记录时间
        private readonly static string LATEST_JHJRECORDTIME = "SELECT MAX([RecordTime]) AS RecordTime, [ProLineCode] FROM [pret].[dbo].[JHJDetail]" +
            "GROUP BY [ProLineCode]";
        #endregion

        #region 电表SQL
        private readonly static string CREATE_METERINFO_TABLE = "CREATE TABLE [pret].[dbo].[MeterInfo](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[Region] [nvarchar](20) NOT NULL CONSTRAINT [DF_MeterInfo_Region]  DEFAULT ((0))," +
            "[PeakUnitPrice] [float] NOT NULL," +
            "[GenUnitPrice] [float] NOT NULL," +
            "[LowUnitPrice] [float] NOT NULL" +
            ") ON [PRIMARY]";
        #endregion

        #region 待产信息SQL
        private readonly static string CREATE_WAITINGORDER_TABLE = "CREATE TABLE [pret].[dbo].[WaitingOrder](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," +
            "[OrderID] [varchar](20) NOT NULL," +
            "[Weight] [float] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string INSERT_WAITINGORDER = "INSERT INTO [pret].[dbo].[WaitingOrder]" +
               "([ProductionLine],[OrderID],[Weight],[ProLineCode])" +
            "VALUES" +
               "('{0}'" + // <ProductionLine, varchar(20),>
               ",'{1}'" + // <OrderID, varchar(20),>
               ",{2}" + // <Weight, float,>
               ",'{3}')"; // <ProLineCode, varchar(10),>
        private readonly static string UPDATE_WAITINGORDER = "UPDATE [pret].[dbo].[WaitingOrder]" +
            "SET [OrderID] = '{1}', [Weight] = {2}" +
            "WHERE [ProLineCode] = '{0}'";
        private readonly static string GET_WAITINGORDER_BY_PROLINE = "SELECT * FROM [pret].[dbo].[WaitingOrder]" +
            "WHERE [ProLineCode] = '{0}'";
        #endregion

        #region 高低柜SQL
        private readonly static string CREATE_HIGHLOW_TABLE = "CREATE TABLE [pret].[dbo].[HighLowInfo](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[DeviceID] [varchar](20) NOT NULL," +
            "[Date] [datetime] NOT NULL," +
            "[MaxCurrent] [float] NOT NULL," +
            "[MinCurrent] [float] NOT NULL," +
            "[MaxVoltage] [float] NOT NULL," +
            "[MinVoltage] [float] NOT NULL," +
            "[MaxLoad] [float] NOT NULL," +
            "[MinLoad] [float] NOT NULL," +
            "[PowerFactor] [float] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_HUMIDITY_TEMPERATURE_TABLE = "CREATE TABLE [pret].[dbo].[HumTemInfo](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[PlaceID] [varchar](20)," +
            "[Date] [datetime] NOT NULL," +
            "[MaxHumidity] [float] NOT NULL," +
            "[MaxTemperature] [float] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_HIGHLOWVOLTAGE_TABLE = "CREATE TABLE [pret].[dbo].[HighLowVoltage](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[DeviceID] [varchar](20) NOT NULL," +
            "[UpdateTime] [datetime] NOT NULL," +
            "[Degree] [float] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string GET_HIGHLOW_BY_DEVICE_TIME = "SELECT * FROM [pret].[dbo].[HighLowInfo]" +
            "WHERE [DeviceID] = '{0}' AND [Date] >= '{1}' AND [Date] <= '{2}'";
        private readonly static string GET_HUMIDITY_TEMPERATURE_BY_TIME = "SELECT * FROM [pret].[dbo].[HumTemInfo]" +
            "WHERE [Date] >= '{0}' AND [Date] <= '{1}'";
        private readonly static string INSERT_HIGHLOW = "INSERT INTO [pret].[dbo].[HighLowInfo]" +
               "([DeviceID],[Date],[MaxCurrent],[MinCurrent],[MaxVoltage],[MinVoltage]," +
               "[MaxLoad],[MinLoad],[PowerFactor])" +
            "VALUES" +
               "('{0}'" + // <DeviceID, varchar(20),>
               ",'{1}'" + // <Date, datetime,>
               ",{2}" + // <MaxCurrent, float,>
               ",{3}" + // <MinCurrent, float,>
               ",{4}" + // <MaxVoltage, float,>
               ",{5}" + // <MinVoltage, float>
               ",{6}" + // <MaxLoad, float>
               ",{7}" + // <MinLoad, float>
               ",{8})"; // <PowerFactor, float>
        private readonly static string INSERT_HIGHLOWVOLTAGE = "INSERT INTO [pret].[dbo].[HighLowVoltage]" +
               "([DeviceID],[UpdateTime],[Degree])" +
            "VALUES" +
               "('{0}'" + // <DeviceID, varchar(20),>
               ",'{1}'" + // <UpdateTime, datetime,>
               ",{2})"; // <Degree, float>
        private readonly static string INSERT_HUMIDITY_TEMPERATURE = "INSERT INTO [pret].[dbo].[HumTemInfo]" +
               "([Date],[MaxHumidity],[MaxTemperature])" +
            "VALUES" +
               "('{0}'" + // <Date, datetime,>
               ",{1}" + // <MaxHumidity, float,>
               ",{2})"; // <MaxTemperature, float,>
        private readonly static string UPDATE_HIGHLOW = "UPDATE [pret].[dbo].[HighLowInfo]" +
            "SET [MaxCurrent]={2},[MinCurrent]={3},[MaxVoltage]={4},[MinVoltage]={5},[MaxLoad]={6},[MinLoad]={7},[PowerFactor]={8} " +
            "WHERE [DeviceID] = '{0}' AND [Date] = '{1}'";
        private readonly static string UPDATE_HUMIDITY_TEMPERATURE = "UPDATE [pret].[dbo].[HumTemInfo]" +
            "SET [MaxHumidity]={1},[MaxTemperature]={2} " +
            "WHERE [Date] = '{0}'";
        private readonly static string LATEST_HIGHLOWTIME = "SELECT MAX([Date]) AS Date, [DeviceID] FROM [pret].[dbo].[HighLowInfo] " +
            "GROUP BY [DeviceID]";
        private readonly static string LATEST_HIGHLOWVOLTAGETIME = "SELECT MAX([UpdateTime]) AS UpdateTime, [DeviceID] FROM [pret].[dbo].[HighLowVoltage] " +
            "GROUP BY [DeviceID]";
        private readonly static string LATEST_HUMTEMTIME = "SELECT MAX([Date]) AS Date FROM [pret].[dbo].[HumTemInfo]";
        #endregion


        #region TV SQL
        private readonly static string CREATE_MESSAGE_TABLE = "CREATE TABLE [pret].[dbo].[MessageTable](" +
            "[msg1] [nvarchar](1024) NULL," +
            "[msg2] [nvarchar](1024) NULL," +
            "[msg3] [nvarchar](1024) NULL," +
            "[msg4] [nvarchar](1024) NULL," +
            "[msg5] [nvarchar](1024) NULL," +
            "[msg6] [nvarchar](1024) NULL," +
            "[msg7] [nvarchar](1024) NULL," +
            "[msg8] [nvarchar](1024) NULL," +
            "[FontSize] [int] NULL CONSTRAINT [DF_MessageTable_FontSize]  DEFAULT ((50))" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_SHOWCONFIG_TABLE = "CREATE TABLE [pret].[dbo].[ShowConfigTable](" +
            "[APP_WORD] [int] NOT NULL," +
            "[APP_CTRL1] [int] NOT NULL," +
            "[APP_CTRL2] [int] NOT NULL," +
            "[APP_CTRL3] [int] NOT NULL" +
            ") ON [PRIMARY]";
        #endregion

        private readonly static string CREATE_IGNOREORDER_TABLE = "CREATE TABLE [pret].[dbo].[IgnoreERPOrderTime](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [varchar](10)," +
            "[OrderID] [varchar](20) NOT NULL" +
            ") ON [PRIMARY]";

        #region ERP Table
        private readonly static string CREATE_EMPLOYE_TABLE = "CREATE TABLE [ERPSystem].[dbo].[Employe](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ID] [varchar](20) NOT NULL," +
            "[Name] [nvarchar](10) NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_PRODUCTIONINFO_TABLE = "CREATE TABLE [ERPSystem].[dbo].[ProductionInfo](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NULL," +
            "[OrderID] [nvarchar](20) NULL," +
            "[ProductName] [nvarchar](256) NULL," +
            "[BatchID] [varchar](20) NULL," +
            "[StockID] [varchar](20) NULL," +
            "[StartTime] [datetime] NULL," +
            "[EndTime] [datetime] NULL," +
            "[Count] [float] NULL CONSTRAINT [DF_ProductionInfo_Count]  DEFAULT ((0))," +
            "[CurrCount] [float] NULL CONSTRAINT [DF_ProductionInfo_CurrCount]  DEFAULT ((0))" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_ORDERFEEDBACK_TABLE = "CREATE TABLE [ERPSystem].[dbo].[OrderFeedback](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [nvarchar](20) NOT NULL," +
            "[OrderID] [nvarchar](20) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime] NOT NULL," +
            "[PowerRate] [float] NOT NULL," +
            "[Efficiency] [float] NOT NULL," +
            "[RongZhi] [float] NOT NULL," +
            "[TeamID] [varchar](20) NOT NULL," +
            "[MixingerID] [varchar](20) NOT NULL," +
            "[ExtruderID] [varchar](20) NOT NULL," +
            "[PackerID] [varchar](20) NOT NULL," +
            "[QualityID] [varchar](20) NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string CREATE_INDEX_PROLINESTATUS = "CREATE INDEX [IX_ProLineStatus] ON [ERPSystem].[dbo].[ProLineStatus] ([ProductionLine] ASC, [RecordTime] ASC)";
        private readonly static string INSERT_ORDERFEEDBACK = "INSERT INTO [ERPSystem].[dbo].[OrderFeedback]" +
            "([OrderID]" +
            ",[PowerRate]" +
            ",[Efficiency]" +
            ",[RongZhi]" +
            ",[TeamID]" +
            ",[MixingerID]" +
            ",[ExtruderID]" +
            ",[PackerID]" +
            ",[QualityID]" +
            ",[ProductionLine]" +
            ",[StartTime]" +
            ",[EndTime]) " +
            "VALUES " +
            "('{0}'" + // <OrderID, varchar(20),>
            ",{1}" + // <PowerRate, float,>
            ",{2}" + // <Efficiency, float,>
            ",{3}" + // <RongZhi, float,>
            ",'{4}'" + // <TeamID, varchar(20),>
            ",'{5}'" + // <MixingerID, varchar(20),>
            ",'{6}'" + // <ExtruderID, varchar(20),>
            ",'{7}'" + // <PackerID, varchar(20),>
            ",'{8}'" + // <QualityID, varchar(20),>
            ",'{9}'" + // <ProductionLine, nvarchar(20),>
            ",'{10}'" + // <StartTime, datetime,>
            ",'{11}')"; // <EndTime, datetime,>
        private readonly static string DELETE_ORDERFEEDBACK = "DELETE FROM [ERPSystem].[dbo].[OrderFeedback] " + 
            "WHERE [OrderID] LIKE '%{0}' AND [ProductionLine] = '{1}' AND [StartTime] = '{2}'";
        private readonly static string GET_ORDERFEEDBACK_FROM_ERP = "SELECT * FROM [ERPSystem].[dbo].[OrderFeedback] " +
            "WHERE [OrderID] LIKE '%{0}' AND [ProductionLine] = '{1}' AND [StartTime] = '{2}'";
        #endregion

        #region 班组SQL
        private readonly static string CREATE_TEAMATTENDANCEHOURS_TABLE = "CREATE TABLE [pret].[dbo].[TeamAttendanceHours](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[Month] [datetime] NOT NULL," +
            "[UpdateTime] [datetime] NOT NULL," +
            "[AHours] [float] DEFAULT ((0))," +
            "[BHours] [float] DEFAULT ((0))," +
            "[CHours] [float] DEFAULT ((0))," +
            "[DHours] [float] DEFAULT ((0))," +
            "[EHours] [float] DEFAULT ((0))," +
            "[FHours] [float] DEFAULT ((0))" +
            ") ON [PRIMARY]";
        #endregion

        #region 滑石母粒机 SQL
        private readonly static string CREATE_HSMLJ_TABLE = "CREATE TABLE [pret].[dbo].[HSMLJData](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[StartTime] [datetime] NOT NULL," +
            "[EndTime] [datetime]," +
            "[UpdateTime] [datetime] NOT NULL," +
            "[MainA] [float] NOT NULL," +
            "[MainB] [float] NOT NULL," +
            "[ExtraA] [float] NOT NULL," +
            "[ExtraB] [float] NOT NULL," +
            "[StartPower] [float] NOT NULL," +
            "[EndPower] [float] NOT NULL" +
            ") ON [PRIMARY]";
        private readonly static string INSERT_HSMLJINFO = "INSERT INTO [pret].[dbo].[HSMLJData]" +
           "([StartTime],[EndTime],[UpdateTime],[MainA],[MainB],[ExtraA],[ExtraB],[StartPower],[EndPower])" +
        "VALUES" +
           "({0}" + // <StartTime, datetime,>
           ",{1}" + // <EndTime, datetime,>
           ",'{2}'" + // <UpdateTime, datetime
           ",{3}" + // <MainA, float,>
           ",{4}" + // <MainB, float,>
           ",{5}" + // <ExtraA, float,>
           ",{6}" + // <ExtraB, float,>
           ",{7}" + // <StartPower, float,>
           ",{8})"; // <EndPower, float>
        private readonly static string LATEST_HSMLJTAILTIME = "SELECT [UpdateTime] FROM [pret].[dbo].[HSMLJData] " +
            "ORDER BY [UpdateTime] DESC";
        private readonly static string GET_HSMLJ_WITHOUT_ENDTIME = "SELECT * FROM [pret].[dbo].[HSMLJData] " +
            "WHERE [EndTime] IS NULL ORDER BY [UpdateTime] DESC";
        private readonly static string UPDATE_HSMLJ_ENDTIME = "UPDATE [pret].[dbo].[HSMLJData]" +
            "SET [EndTime]='{1}',[UpdateTime]='{2}',[MainA]={3},[MainB]={4},[ExtraA]={5},[ExtraB]={6},[EndPower]={7} " +
            "WHERE [StartTime] = '{0}'";
        private readonly static string UPDATE_HSMLJ_NOENDTIME = "UPDATE [pret].[dbo].[HSMLJData]" +
            "SET [UpdateTime]='{1}',[MainA]={2},[MainB]={3},[ExtraA]={4},[ExtraB]={5},[EndPower]={6} " +
            "WHERE [StartTime] = '{0}'";
        #endregion

        #region 效率相关Table
        private readonly static string CREATE_EFFIQUOTA_TABLE = "CREATE TABLE [pret].[dbo].[EfficiencyQuota](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[TypeName] [varchar](10)," +
            "[EffType] [int]," +
            "[EffQuota] [float] NOT NULL" +
            ") ON [PRIMARY]";

        private readonly static string CREATE_PRODUCE_TASK_TABLE = "CREATE TABLE [pret].[dbo].[ProduceTask](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductLine] [varchar](50) NOT NULL," +
            "[CurrentProductName] [varchar](256) NULL," +
            "[CurrentWeight] [float] NULL," +
            "[StartTime] [datetime] NULL," +
            "[CurrentEstimateEndTime] [datetime] NULL," +
            "[CurrentOrderID] [varchar](50) NULL," +
            "[TeamName] [varchar](256) NULL," +
            "[MixingerName] [varchar](50) NULL," +
            "[ExtruderName] [varchar](50) NULL," +
            "[PackerName] [varchar](50) NULL," +
            "[QualityName] [varchar](50) NULL," +
            "[ProductionEfficiency] [float] NULL," +
            "[Weight] [float] NULL," +
            "[TotalPower] [float] NULL," +
            "[TotalEnergy] [float] NULL" +
            ") ON [PRIMARY]";
        private readonly static string SELECT_A_PRODUCE_TASK = "SELECT * FROM [pret].[dbo].[ProduceTask] WHERE [ProductLine] = '{0}'";
        private readonly static string INSERT_PRODUCE_TASK = "INSERT INTO [pret].[dbo].[ProduceTask]" +
            "([ProductLine]" +
            ",[CurrentProductName]" +
            ",[CurrentWeight]" +
            ",[StartTime]" +
            ",[CurrentEstimateEndTime]" +
            ",[CurrentOrderID]" +
            ",[TeamName]" +
            ",[MixingerName]" +
            ",[ExtruderName]" +
            ",[PackerName]" +
            ",[QualityName]" +
            ",[ProductionEfficiency]" +
            ",[Weight]" +
            ",[TotalPower]" +
            ",[TotalEnergy])" +
            "VALUES" +
            "('{0}'," + //<ProductLine, varchar(50),>
            "'{1}'," + //<CurrentProductName, varchar(256),>
            "{2}," + //<CurrentWeight, float,>
            "{3}," + //<StartTime, datetime,>
            "{4}," + //<CurrentEstimateEndTime, datetime,>
            "'{5}'," + //<CurrentOrderID, varchar(50),>
            "'{6}'," + //<TeamName, varchar(256),>
            "'{7}'," + //<MixingerName, varchar(50),>
            "'{8}'," + //<ExtruderName, varchar(50),>
            "'{9}'," + //<PackerName, varchar(50),>
            "'{10}'," + //<QualityName, varchar(50),>
            "{11}," + //<ProductionEfficiency, float,>
            "{12}," + //<Weight, float,>
            "{13}," + //<TotalPower, float,>
            "{14})"; //<TotalEnergy, float,>)
        private readonly static string UPDATE_PRODUCE_TASK = "UPDATE [pret].[dbo].[ProduceTask]" + 
            "SET [ProductLine] = '{0}',[CurrentProductName] = '{1}',[CurrentWeight] = {2},[StartTime] = {3}" +
            ",[CurrentEstimateEndTime] = {4},[CurrentOrderID] = '{5}',[TeamName] = '{6}'" +
            ",[MixingerName] = '{7}',[ExtruderName] = '{8}',[PackerName] = '{9}',[QualityName] = '{10}'" +
            ",[ProductionEfficiency] = {11},[Weight] = {12},[TotalPower] = {13},[TotalEnergy] = {14}" +
            "WHERE [ProductLine] = '{0}'";
        #endregion

        #region 待机相关Table
        private readonly static string CREATE_STANDBYPROLINE_TABLE = "CREATE TABLE [pret].[dbo].[StandbyProLine](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProductionLine] [varchar](10)," +
            "[Year] [int] NOT NULL DEFAULT ((0))," +
            "[JanTime] [float] NOT NULL DEFAULT ((0))," +
            "[FebTime] [float] NOT NULL DEFAULT ((0))," +
            "[MarTime] [float] NOT NULL DEFAULT ((0))," +
            "[AprTime] [float] NOT NULL DEFAULT ((0))," +
            "[MayTime] [float] NOT NULL DEFAULT ((0))," +
            "[JunTime] [float] NOT NULL DEFAULT ((0))," +
            "[JulTime] [float] NOT NULL DEFAULT ((0))," +
            "[AugTime] [float] NOT NULL DEFAULT ((0))," +
            "[SepTime] [float] NOT NULL DEFAULT ((0))," +
            "[OctTime] [float] NOT NULL DEFAULT ((0))," +
            "[NovTime] [float] NOT NULL DEFAULT ((0))," +
            "[DecTime] [float] NOT NULL DEFAULT ((0))," +
            "[MaxTime] [float] NOT NULL DEFAULT ((0))," +
            "[MinTime] [float] NOT NULL DEFAULT ((0))" +
            ") ON [PRIMARY]";

        private readonly static string GET_PROLINESTANDBY = "SELECT * FROM [pret].[dbo].[StandbyProLine] WHERE [ProductionLine] = '{0}' AND [Year] = {1}";
        private readonly static string INSERT_PROLINESTANDBY = "INSERT INTO [pret].[dbo].[StandbyProLine]([ProductionLine],[Year]," +
            "[JanTime]," +
            "[FebTime]," +
            "[MarTime]," +
            "[AprTime]," +
            "[MayTime]," +
            "[JunTime]," +
            "[JulTime]," +
            "[AugTime]," +
            "[SepTime]," +
            "[OctTime]," +
            "[NovTime]," +
            "[DecTime],[MaxTime],[MinTime]) " +
            "VALUES " +
            "('{0}'," + // <ProductionLine, nvarchar(20),>
            "{1}," + // <Year, int,>
            "{2}," + // <JanTime, float,>
            "{3}," + // <FebTime, float,>
            "{4}," + // <MarTime, float,>
            "{5}," + // <AprTime, float,>
            "{6}," + // <MayTime, float,>
            "{7}," + // <JunTime, float,>
            "{8}," + // <JulTime, float,>
            "{9}," + // <AugTime, float,>
            "{10}," + // <SepTime, float,>
            "{11}," + // <OctTime, float,>
            "{12}," + // <NovTime, float,>
            "{13}," + // <DecTime, float,>
            "{14}," + // <MaxTime, float,>
            "{15})"; // <MinTime, float,>
        private readonly static string UPDATE_PROLINESTANDBY = "UPDATE [pret].[dbo].[StandbyProLine] " +
            "SET [ProductionLine] = '{0}',[Year] = {1}," +
            "[JanTime] = {2},[FebTime] = {3},[MarTime] = {4}," +
            "[AprTime] = {5},[MayTime] = {6},[JunTime] = {7}," +
            "[JulTime] = {8},[AugTime] = {9},[SepTime] = {10}," +
            "[OctTime] = {11},[NovTime] = {12},[DecTime] = {13}," +
            "[MaxTime] = {14},[MinTime] = {15} " +
            "WHERE [ProductionLine] = '{0}' AND [Year] = {1}";
        #endregion


        //public bool IsConnected
        //{
        //    get
        //    {
        //        if (conn.State == System.Data.ConnectionState.Closed || conn.State == System.Data.ConnectionState.Broken)
        //            return false;
        //        else
        //            return true;
        //    }
        //}

        private System.Diagnostics.EventLog log;

        private string serverAddress = "";
        private string dbName = "";
        private string userid = "";
        private string password = "";
        private string ConnString = "";

        public SQLDataProcess(string serverAddress, string dbName, string userid, string password, EventLog log)
        {
            this.serverAddress = serverAddress;
            this.dbName = dbName;
            this.userid = userid;
            this.password = password;
            this.ConnString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
            this.log = log;
            //if (IsConnected == false)
            //    ConnectSQL();
        }
        
        public void Dispose()
        {
            //try
            //{
            //    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
            //    {
            //        conn.Close();
            //    }
            //}
            //catch (Exception ex)
            //{
            //    string msg = "SQLDataProcess.Dispose() Failed: " + ex.Message;
            //    log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            //}
        }

        //public void ConnectSQL()
        //{
        //    try
        //    {
        //        conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
        //        conn.Open();
        //    }
        //    catch (Exception ex)
        //    {
        //        string msg = "SQLDataProcess.ConnectSQL() Failed: " + ex.Message;
        //        log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
        //    }
        //}

        private static object lockObj = new object();
        //public void ReConnectSQL()
        //{
        //    lock (lockObj)
        //    {
        //        if (IsConnected == false)
        //            ConnectSQL();
        //    }
        //}
        #region 创建DB和Table
        public void CreatePRETDB()
        {
            this.CreateDB();
            this.CreateIgnoreERPOrderTimeTable();
            this.CreateWaitingOrderTable();
            this.CreateJCJInfoTable();
            this.CreateJCJDetailTable();
            this.CreateJHJInfoTable();
            this.CreateJHJDetailTable();
            this.CreateJHJTransTable();
            this.CreateJHJHeatingTable();
            this.CreateJHJPackTable();
            //this.CreateJHJThrowTable();
            this.CreateMeterInfoTable();
            this.CreateTeamWorkTimeTable();
            this.CreateHigLowTable();
            this.CreateHigLowVoltageTable();
            this.CreateHumidityTemperatureTable();
            this.CreateProlineEfficiency();
            //this.CreateJCJSummaryVariableTable();
            //this.CreateJCJDetailVariableTable();
            //this.CreateJHJDataVariableTable();
            //this.CreateTeamVariableTable();
            //this.CreateWaitingOrderVariableTable();
            this.CreateMessageTable();
            this.CreateShowConfigTable();
            this.CreateTeamAttendanceHoursTable();
            this.CreateHSMLJTable();
            this.CreateEfficiencyQuotaTable();
            this.CreateStandbyProlineTable();

            this.CreateERPSystemDB();
            this.CreateEmployeTable();
            this.CreateProductionInfoTable();
            this.CreateOrderFeedbackTable();
            this.CreateProduceTaskTable();

            //创建索引
            this.CreateJCJDetailIndex();
            this.CreateJHJDetailIndex();
            this.CreateERPProLineStatusIndex();
        }
        private void CreateDB()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_PRET_DB, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery(); 
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreatePERTDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateERPSystemDB()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_ERPSystem_DB, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery(); 
                }
            }
            catch(SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateERPSystemDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateERPProLineStatusIndex()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_INDEX_PROLINESTATUS, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery(); 
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateERPProLineStatusIndex() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建IgnoreERPOrderTime表
        /// </summary>
        private void CreateIgnoreERPOrderTimeTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_IGNOREORDER_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateIgnoreERPOrderTimeTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateWaitingOrderTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_WAITINGORDER_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateWaitingOrderTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateEmployeTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_EMPLOYE_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateEmployeTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateProductionInfoTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_PRODUCTIONINFO_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateProductionInfoTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateOrderFeedbackTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_ORDERFEEDBACK_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateOrderFeedbackTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateMessageTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_MESSAGE_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateMessageTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建EfficiencyQuota表
        /// </summary>
        private void CreateEfficiencyQuotaTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_EFFIQUOTA_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateEfficiencyQuotaTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建ProduceTask表
        /// </summary>
        private void CreateProduceTaskTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_PRODUCE_TASK_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateEfficiencyQuotaTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建Standby Proline表
        /// </summary>
        private void CreateStandbyProlineTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_STANDBYPROLINE_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateStandbyProlineTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateShowConfigTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_SHOWCONFIG_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateShowConfigTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateTeamAttendanceHoursTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_TEAMATTENDANCEHOURS_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateTeamAttendanceHoursTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }


        #endregion

        #region 挤出机相关DB操作
        /// <summary>
        /// 在pret DB中创建JCJInfo表
        /// </summary>
        public void CreateJCJInfoTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JCJINFO_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateJCJInfoTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建JCJDetail表
        /// </summary>
        public void CreateJCJDetailTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JCJDETAIL_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateJCJDetailTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        public void CreateJCJDetailIndex()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_INDEX_JCJDETAIL, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateJCJDetailIndex() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 添加JCJData数据到JCJInfo表中
        /// </summary>
        /// <param name="obj"></param>
        public void AddJCJDataInfo(JCJDataInfo obj)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                if (obj == null)
                    return;

                // 保存JCJSummaryInfo的数据
                if(obj.JCJSummaryInfo != null && obj.JCJSummaryInfo.Count > 0)
                {
                    foreach(JCJSummaryInfo summary in obj.JCJSummaryInfo)
                    {
                        AddUpdateJCJSummaryInfo(summary);
                    }
                }
                // 保存JCJDetail的数据
                if (obj.JCJDetailInfo != null && obj.JCJDetailInfo.Count > 0)
                {
                    foreach(JCJDetailInfo detail in obj.JCJDetailInfo)
                    {
                        AddJCJDetailInfo(detail);
                    }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddJCJDataInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
        }
        private void AddUpdateJCJSummaryInfo(JCJSummaryInfo newInfo)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                JCJSummaryInfo existObj = GetJCJSummaryInfoByOrderID(newInfo.OrderID, newInfo.ProLineCode);
                // 如果数据库中找不到指定订单的挤出机数据，那么添加数据到JCJInfo表格中
                if (existObj == null && newInfo.StartTime != null)
                {
                    AddJCJSummaryInfoInDB(newInfo);
                }
                // 如果有存在的订单，更新这个订单
                else if (existObj != null)
                {
                    // 如果这个订单在记录里显示已经结束，那么根据结束时间和新订单的开始时间来判断是否更新已有记录
                    // 还是添加一个新的记录
                    if (existObj.EndTime != null && newInfo.StartTime != null)
                    {
                        TimeSpan span = (TimeSpan)(newInfo.StartTime - existObj.EndTime);
                        // 如果同一个订单的开始时间和它上一次的结束时间超过半小时，那么添加这个订单作为新的一行
                        if (span.TotalHours > 0.25)
                        {
                            AddJCJSummaryInfoInDB(newInfo);
                            return;
                        }
                        else
                        {
                            string endTime = "null";
                            if (newInfo.EndTime != null)
                                endTime = "'" + newInfo.EndTime.ToString() + "'";
                            string updatetime = "'" + newInfo.UpdateTime.ToString() + "'";
                            string update = string.Format(UPDATE_JCJSUMMARY_ENDTIME, newInfo.ProLineCode, existObj.OrderID, existObj.StartTime, endTime, updatetime);
                            using (SqlCommand cmd = new SqlCommand(update, conn))
                            {
                                cmd.CommandTimeout = 0; 
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    else
                    {
                        string endTime = "null";
                        if (newInfo.EndTime != null)
                            endTime = "'" + newInfo.EndTime.ToString() + "'";
                        string updatetime = "'" + newInfo.UpdateTime.ToString() + "'";
                        string update = string.Format(UPDATE_JCJSUMMARY_ENDTIME, newInfo.ProLineCode, existObj.OrderID, existObj.StartTime, endTime, updatetime);
                        using (SqlCommand cmd = new SqlCommand(update, conn))
                        {
                            cmd.CommandTimeout = 0; 
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddUpdateJCJSummaryInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void AddJCJSummaryInfoInDB(JCJSummaryInfo obj)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                string start = "null";
                if (obj.StartTime != null)
                    start = "'" + obj.StartTime.ToString() + "'";
                string end = "null";
                if (obj.EndTime != null)
                    end = "'" + obj.EndTime.ToString() + "'";
                string insert = string.Format(INSERT_JCJSUMMARYINFO, obj.ProductionLine, obj.ProLineCode, 
                    obj.OrderID, start, end, obj.UpdateTime);
                using (SqlCommand cmd = new SqlCommand(insert, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddJCJSummaryInfoInDB() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void AddJCJDetailInfo(JCJDetailInfo obj)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                if (obj == null)
                    return;
                string insert = string.Format(INSERT_JCJDETAIL, obj.RecordTime, obj.UpdateTime, obj.ProductionLine, obj.ProLineCode,
                obj.Bal1Scale, obj.Bal2Scale, obj.Bal3Scale, obj.Bal1RealCount, obj.Bal2RealCount, obj.Bal3RealCount, obj.WLJRevolvingSpeed,
                obj.JCJ1TempOffset, obj.JCJ2TempOffset, obj.JCJ3TempOffset, obj.JCJ4TempOffset, obj.JCJ5TempOffset, obj.JCJ6TempOffset, obj.JCJ7TempOffset, obj.JCJ8TempOffset, obj.JCJ9TempOffset, obj.JCJ10TempOffset,
                obj.JCJ1SetTemp, obj.JCJ2SetTemp, obj.JCJ3SetTemp, obj.JCJ4SetTemp, obj.JCJ5SetTemp, obj.JCJ6SetTemp, obj.JCJ7SetTemp, obj.JCJ8SetTemp, obj.JCJ9SetTemp, obj.JCJ10SetTemp,
                obj.JCJ1RealTemp, obj.JCJ2RealTemp, obj.JCJ3RealTemp, obj.JCJ4RealTemp, obj.JCJ5RealTemp, obj.JCJ6RealTemp, obj.JCJ7RealTemp, obj.JCJ8RealTemp, obj.JCJ9RealTemp, obj.JCJ10RealTemp,
                obj.ExtruderMaxRevolvingSpeed, obj.ExtruderMinRevolvingSpeed, obj.ExtruderRevolvingRealSpeed,
                obj.ExtruderMaxCurrent, obj.ExtruderMinCurrent, obj.ExtruderRealCurrent,
                obj.MaxRongZhi, obj.MinRongZhi, obj.RealRongZhi, obj.MaxRemnant, obj.MinRemnant, obj.RealRemnant,
                obj.ElectricPower, obj.MaxCurrent, obj.MinCurrent, obj.Current, obj.MaxPower, obj.JsProduce);
                using (SqlCommand cmd = new SqlCommand(insert, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddJCJDetailInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        public Dictionary<string, DateTime> GetLatestJCJTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                Dictionary<string, DateTime> retDic = new Dictionary<string, DateTime>();
                using (cmd = new SqlCommand(LATEST_JCJDETAILTIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string strTime = reader["UpdateTime"].ToString();
                        string prolineCode = reader["ProLineCode"].ToString();
                        if (!string.IsNullOrEmpty(strTime) && !string.IsNullOrEmpty(prolineCode))
                        {
                            retDic.Add(prolineCode, DateTime.Parse(strTime).AddSeconds(1));
                        }
                    }
                    reader.Close();
                }
                return retDic;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestJCJTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private JCJSummaryInfo GetJCJSummaryInfoByOrderID(string OrderID, string prolineCode)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                JCJSummaryInfo obj = null;
                string latestID = "";
                DateTime? updateTime = null;
                DateTime? startTime = null;
                // 先取出未完成时间的订单数据
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                string query = string.Format(GET_JCJINFO_BY_ORDERID_WITHOUT_ENDTIME, prolineCode);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        latestID = reader["OrderID"].ToString();
                        if (latestID == OrderID)
                        {
                            obj = new JCJSummaryInfo();
                            obj.ProductionLine = reader["ProductionLine"].ToString();
                            obj.ProLineCode = reader["ProLineCode"].ToString();
                            obj.OrderID = reader["OrderID"].ToString();
                            if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                                obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                            obj.EndTime = null;
                            obj.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        }
                        else
                        {
                            startTime = DateTime.Parse(reader["StartTime"].ToString());
                            updateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        }
                    }
                    reader.Close();
                }

                // 先把上次没有结束的订单关掉
                if (updateTime != null && startTime != null)
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    string updatetime = "'" + (DateTime)updateTime + "'";
                    query = string.Format(UPDATE_JCJSUMMARY_ENDTIME, prolineCode, latestID, (DateTime)startTime, updatetime, updatetime);
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery(); 
                    }
                    conn.Close();
                    return null;
                }
                if (obj != null)
                    return obj;

                // 如果当前的订单没有未完成的记录，返回最近一次的订单信息
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                query = string.Format(GET_JCJINFO_BY_ORDERID_WITH_ENDTIME, prolineCode);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        latestID = reader["OrderID"].ToString();
                        if (latestID == OrderID)
                        {
                            obj = new JCJSummaryInfo();
                            obj.ProductionLine = reader["ProductionLine"].ToString();
                            obj.ProLineCode = reader["ProLineCode"].ToString();
                            obj.OrderID = reader["OrderID"].ToString();
                            if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                                obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                            if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                                obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                            obj.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        }
                    }
                    reader.Close();
                }
                return obj;
            }
            catch(Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJCJSummaryInfoByOrderID() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        #endregion

        #region 均化机相关DB操作
        /// <summary>
        /// 获得均化机记录表中最近一次的记录时间，如果没有任何记录返回DateTime.MinValue
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, DateTime> GetLatestJHJTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                Dictionary<string, DateTime> retDic = new Dictionary<string, DateTime>();
                using (cmd = new SqlCommand(LATEST_JHJRECORDTIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string strTime = reader["RecordTime"].ToString();
                        string prolineCode = reader["ProLineCode"].ToString();
                        if (!string.IsNullOrEmpty(strTime) && !string.IsNullOrEmpty(prolineCode))
                        {
                            retDic.Add(prolineCode, DateTime.Parse(strTime).AddSeconds(1));
                        }
                    }
                    reader.Close();
                }
                return retDic;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestJHJTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }

        /// <summary>
        /// 在pret DB中创建JHJInfo表
        /// </summary>
        public void CreateJHJInfoTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JHJINFO_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJInfoTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建JHJDetail表
        /// </summary>
        public void CreateJHJDetailTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JHJDETAIL_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJDetailTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 创建JHJDetail索引
        /// </summary>
        private void CreateJHJDetailIndex()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_INDEX_JHJDETAIL, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateJHJDetailIndex() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建JHJTransRecordTime表
        /// </summary>
        public void CreateJHJTransTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JHJTRANS_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJTransTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建JHJHeatingRecordTime表
        /// </summary>
        public void CreateJHJHeatingTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JHJHEATING_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJHeatingTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 在pret DB中创建JHJPackRecordTime表
        /// </summary>
        public void CreateJHJPackTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JHJPACKING_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJPackTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        public void CreateJHJThrowTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_JHJTHROW_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJThrowTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 添加JHJData数据到均化机相关表中——JHJTransRecordTime, JHJPackRecordTime, JHJHeatingRecordTime
        /// </summary>
        /// <param name="obj"></param>
        public void AddJHJInfo(JHJDataInfo obj)
        {
            try
            {
                if (obj == null)
                    return;
                if (obj.JHJInfoList != null && obj.JHJInfoList.Count > 0)
                {
                    foreach(JHJData data in obj.JHJInfoList)
                    {
                        AddUpdateJHJInfo(data);
                        AddUpdateJHJTransRecordTime(data.ProductionLine, data.ProLineCode, data.ABTimeList);
                        AddUpdateJHJHeatingRecordTime(data.ProductionLine, data.ProLineCode, data.BHeatingTimeList);
                        AddUpdateJHJPackRecordTime(data.ProductionLine, data.ProLineCode, data.BPackingTimeList);
                    }
                }
                if (obj.JHJDetailList != null && obj.JHJDetailList.Count > 0)
                {
                    foreach(JHJDetailInfo data in obj.JHJDetailList)
                    {
                        AddJHJDetailInfo(data);
                    }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddJHJInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
        }

        private void AddUpdateJHJInfo(JHJData newInfo)
        {
            // 1. 从JHJInfo Table中获取指定OrderID的均化机数据
            JHJData existInfo = GetJHJDataByOrderID(newInfo.OrderID, newInfo.ProLineCode);
            // 2. 更新JHJInfo Table中的指定OrderID数据
            if (existInfo == null && newInfo.StartTime != null)
                AddJHJDataInDB(newInfo);
            else
            {
                // 如果新的均化机有结束时间记录的话，更新指定OrderID的结束时间
                if (existInfo != null)
                {
                    // 如果这个订单在记录里显示已经结束，那么根据结束时间和新订单的开始时间来判断是否更新已有记录
                    // 还是添加一个新的记录
                    if(existInfo.EndTime != null && newInfo.StartTime != null)
                    {
                        TimeSpan span = (TimeSpan)(newInfo.StartTime - existInfo.EndTime);
                        if (span.TotalHours > 0.25)
                        {
                            AddJHJDataInDB(newInfo);
                        }
                        else
                        {
                            string endTime = "null";
                            if (newInfo.EndTime != null)
                                endTime = "'" + newInfo.EndTime.ToString() + "'";
                            string updatetime = "'" + newInfo.UpdateTime.ToString() + "'";
                            string update = string.Format(UPDATE_JHJENDTIME, newInfo.ProductionLine, existInfo.OrderID, existInfo.StartTime, endTime, updatetime);
                            SqlConnection conn = new SqlConnection();
                            conn.ConnectionString = this.ConnString;
                            conn.Open();
                            using (SqlCommand cmd = new SqlCommand(update, conn))
                            {
                                cmd.CommandTimeout = 0; 
                                cmd.ExecuteNonQuery();
                            }
                            conn.Close();
                        }
                    }
                    else
                    {
                        string endTime = "null";
                        if (newInfo.EndTime != null)
                            endTime = "'" + newInfo.EndTime.ToString() + "'";
                        string updatetime = "'" + newInfo.UpdateTime.ToString() + "'";
                        string update = string.Format(UPDATE_JHJENDTIME, newInfo.ProductionLine, existInfo.OrderID, existInfo.StartTime, endTime, updatetime);
                        SqlConnection conn = new SqlConnection();
                        conn.ConnectionString = this.ConnString;
                        conn.Open();
                        using (SqlCommand cmd = new SqlCommand(update, conn))
                        {
                            cmd.CommandTimeout = 0; 
                            cmd.ExecuteNonQuery();
                        }
                        conn.Close();
                    }
                }
            }
        }
        private JHJData GetJHJDataByOrderID(string orderID, string prolineCode)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                JHJData obj = null;
                string latestID = "";
                DateTime? updateTime = null;
                DateTime? startTime = null;
                string productionLine = "";

                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                // 先取出未完成时间的订单数据
                string query = string.Format(GET_JHJINFO_BY_ORDERID_WITHOUT_ENDTIME, prolineCode);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        latestID = reader["OrderID"].ToString();
                        if (latestID == orderID)
                        {
                            obj = new JHJData();
                            obj.ProductionLine = reader["ProductionLine"].ToString();
                            obj.ProLineCode = reader["ProLineCode"].ToString();
                            obj.OrderID = reader["OrderID"].ToString();
                            if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                                obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                            obj.EndTime = null;
                            obj.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        }
                        else
                        {
                            productionLine = reader["ProductionLine"].ToString();
                            startTime = DateTime.Parse(reader["StartTime"].ToString());
                            updateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        }
                    }
                    reader.Close();
                }
                // 先把上次没有结束的订单关掉
                if (updateTime != null && startTime != null)
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    string updatetime = "'" + (DateTime)updateTime + "'";
                    query = string.Format(UPDATE_JHJENDTIME, productionLine, latestID, (DateTime)startTime, updatetime, updatetime);
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery(); 
                    }
                    conn.Close();
                    return null;
                }
                if (obj != null)
                    return obj;
                query = string.Format(GET_JHJINFO_BY_ORDERID_WITH_ENDTIME, prolineCode);
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        obj = new JHJData();
                        obj.ProductionLine = reader["ProductionLine"].ToString();
                        obj.ProLineCode = reader["ProLineCode"].ToString();
                        obj.OrderID = reader["OrderID"].ToString();
                        if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                            obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                            obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                        obj.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                    }
                    reader.Close();
                }
                return obj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJDataByOrderID() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private void AddJHJDataInDB(JHJData data)
        {
            try
            {
                if (data == null)
                    return;
                string start = "null";
                if (data.StartTime != null)
                    start = "'" + data.StartTime.ToString() + "'";
                string end = "null";
                if (data.EndTime != null)
                    end = "'" + data.EndTime.ToString() + "'";
                string insert = string.Format(INSERT_JHJDATA, data.ProductionLine, data.ProLineCode, data.OrderID,
                    start, end, data.UpdateTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(insert, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddJHJDataInDB() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
        }

        private void AddUpdateJHJTransRecordTime(string productionLine, string prolineCode, List<ABRecordTime> recordTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                if (recordTime == null || recordTime.Count == 0)
                    return;

                // 从DB中获得只有开始时间没有结束时间的A桶到B桶时间记录
                int startIndex = 0;
                ABRecordTime time = null;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                string query = string.Format(GET_JHJTRANS_BY_ORDERID_NOENDTIME, prolineCode);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        // 取出当前第一个含有结束时间标记的数据
                        while (startIndex < recordTime.Count)
                        {
                            if (recordTime[startIndex].EndTime != null)
                            {
                                time = new ABRecordTime();
                                if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                                    time.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                                time.EndTime = recordTime[startIndex].EndTime;
                                startIndex++;
                                break;
                            }
                            startIndex++;
                        }
                    }
                    reader.Close();
                }
                if (time != null)
                    UpdateJHJTransNoEndTime(productionLine, time);

                // 添加新的记录时间到DB中
                while (startIndex < recordTime.Count)
                {
                    // 如果一个数据的开始时间为空则跳过这个记录
                    if (recordTime[startIndex].StartTime != null)
                        AddJHJTransData(productionLine, prolineCode, recordTime[startIndex]);
                    startIndex++;
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddUpdateJHJTransRecordTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }
        private void UpdateJHJTransNoEndTime(string productionLine, ABRecordTime recordTime)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                string query = string.Format(UPDATE_JHJTRANSENDTIME, productionLine, recordTime.EndTime, recordTime.StartTime);
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string msg = "SQLDataProcess.UpdateJHJTransNoEndTime() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void AddJHJTransData(string productionLine, string prolineCode, ABRecordTime recordTime)
        {
            SqlCommand cmd = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                if (recordTime.StartTime == null)
                    return;
                string start = "'" + recordTime.StartTime.ToString() + "'";
                string end = "null";
                if (recordTime.EndTime != null)
                    end = "'" + recordTime.EndTime.ToString() + "'";
                string insert = string.Format(INSERT_JHJTRANSDATA, productionLine, prolineCode, start, end);
                using (cmd = new SqlCommand(insert, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddJHJTransData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void AddUpdateJHJHeatingRecordTime(string productionLine, string prolineCode, List<BHeatingRecordTime> recordTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                if (recordTime == null || recordTime.Count == 0)
                    return;

                // 从DB中获得只有开始时间没有结束时间的记录
                BHeatingRecordTime time = null;
                int startIndex = 0;
                string query = string.Format(GET_JHJHEATING_BY_ORDERID_NOENDTIME, prolineCode);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        // 取出当前第一个含有结束时间标记的数据
                        while (startIndex < recordTime.Count)
                        {
                            if (recordTime[startIndex].EndTime != null)
                            {
                                time = new BHeatingRecordTime();
                                if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                                    time.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                                time.EndTime = recordTime[startIndex].EndTime;
                                startIndex++;
                                break;
                            }
                            startIndex++;
                        }
                    }
                    reader.Close();
                }
                if (time != null)
                    UpdateJHJHeatingNoEndTime(productionLine, time);

                // 添加新的记录时间到DB中
                while (startIndex < recordTime.Count)
                {
                    // 如果一个数据的开始时间为空则跳过这个记录
                    if (recordTime[startIndex].StartTime != null)
                        AddJHJHeatingData(productionLine, prolineCode, recordTime[startIndex]);
                    startIndex++;
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddUpdateJHJHeatingRecordTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }
        private void UpdateJHJHeatingNoEndTime(string productionLine, BHeatingRecordTime recordTime)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                string query = string.Format(UPDATE_JHJHEATINGENDTIME, productionLine, recordTime.EndTime, recordTime.StartTime);
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string msg = "SQLDataProcess.UpdateJHJHeatingNoEndTime() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void AddJHJHeatingData(string productionLine, string prolineCode, BHeatingRecordTime recordTime)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (recordTime.StartTime == null)
                    return;
                string start = "'" + recordTime.StartTime.ToString() + "'";
                string end = "null";
                if (recordTime.EndTime != null)
                    end = "'" + recordTime.EndTime.ToString() + "'";
                string insert = string.Format(INSERT_JHJHEATINGDATA, productionLine, prolineCode, start, end);
                using (cmd = new SqlCommand(insert, conn))
                { cmd.ExecuteNonQuery(); }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddJHJHeatingData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void AddUpdateJHJPackRecordTime(string productionLine, string prolineCode, List<BPackingRecordTime> recordTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                if (recordTime == null || recordTime.Count == 0)
                    return;

                // 从DB中获得只有开始时间没有结束时间的记录
                int startIndex = 0;
                BPackingRecordTime time = null;
                string query = string.Format(GET_JHJPACK_BY_ORDERID_NOENDTIME, prolineCode);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        // 取出当前第一个含有结束时间标记的数据
                        while (startIndex < recordTime.Count)
                        {
                            if (recordTime[startIndex].EndTime != null)
                            {
                                time = new BPackingRecordTime();
                                time.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                                time.EndNum = recordTime[startIndex].EndNum;
                                time.EndTime = recordTime[startIndex].EndTime;
                                startIndex++;
                                break;
                            }
                            startIndex++;
                        }
                    }
                    reader.Close();
                    if (time != null)
                    {
                        UpdateJHJPackNoEndTime(productionLine, time);
                    }
                }

                // 添加新的记录时间到DB中
                while (startIndex < recordTime.Count)
                {
                    if (recordTime[startIndex].StartTime != null)
                        AddJHJPackData(productionLine, prolineCode, recordTime[startIndex]);
                    startIndex++;
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddUpdateJHJPackRecordTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }
        private void UpdateJHJPackNoEndTime(string productionLine, BPackingRecordTime recordTime)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                string query = string.Format(UPDATE_JHJPACKENDTIME_WITH_ENDTIME, productionLine, recordTime.EndTime, recordTime.StartTime);
                if (!string.IsNullOrEmpty(query))
                {
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0; 
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                string msg = "SQLDataProcess.UpdateJHJPackNoEndTime() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }

        }
        private List<BThrowRecordTime> GetJHJThrowInfo(string orderID, DateTime startTime, DateTime endTime)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                List<BThrowRecordTime> retList = new List<BThrowRecordTime>();
                string query = string.Format(GET_JHJTHROWINFO, orderID, startTime, endTime);
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
                {
                    DataTable retTable = new DataTable();
                    adapter.Fill(retTable);
                    for (int i = 0; i < retTable.Rows.Count; i++)
                    {
                        BThrowRecordTime record = new BThrowRecordTime();
                        if (retTable.Rows[i].ItemArray[2] != null && retTable.Rows[i].ItemArray[2].ToString().Length > 0)
                            record.StartTime = (DateTime)retTable.Rows[i].ItemArray[2];
                        if (retTable.Rows[i].ItemArray[3] != null && retTable.Rows[i].ItemArray[3].ToString().Length > 0)
                            record.EndTime = (DateTime)retTable.Rows[i].ItemArray[3];
                        record.Weight = float.Parse(retTable.Rows[i].ItemArray[4].ToString());
                        retList.Add(record);
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetJHJThrowInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        private void AddJHJPackData(string productionLine, string prolineCode, BPackingRecordTime recordTime)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (recordTime.StartTime == null)
                    return;
                string start = "'" + recordTime.StartTime.ToString() + "'";
                string end = "null";
                if (recordTime.EndTime != null)
                    end = "'" + recordTime.EndTime.ToString() + "'";
                string insert = string.Format(INSERT_JHJPACKDATA, productionLine, prolineCode, start, end);
                using (cmd = new SqlCommand(insert, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery(); 
                }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddJHJPackData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
            finally
            {
                conn.Close();
            }
        }
        private void AddJHJDetailInfo(JHJDetailInfo detail)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (detail != null)
                {
                    string insert = string.Format(INSERT_JHJDETAIL, detail.ProductionLine, detail.ProLineCode, detail.RecordTime,
                        detail.ElectricPower, detail.MaxCurrent, detail.MinCurrent, detail.MaxPower,
                        detail.BBoxZDTemper, detail.ABoxTemper, detail.PackNumber);
                    using (cmd = new SqlCommand(insert, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddJHJDetailInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 电表相关DB操作
        /// <summary>
        /// 在pret DB中创建电表相关Table
        /// </summary>
        public void CreateMeterInfoTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_METERINFO_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateMeterInfoTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 待产信息相关DB操作
        public void AddOrUpdateWaitingOrder(WaitingOrder order)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                bool canUpdate = false;
                string query = string.Format(GET_WAITINGORDER_BY_PROLINE, order.ProLineCode);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using(cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                        canUpdate = true;
                    reader.Close();
                }

                if(canUpdate)
                {
                    query = string.Format(UPDATE_WAITINGORDER, order.ProLineCode, order.OrderID, order.Weight);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                else
                {
                    query = string.Format(INSERT_WAITINGORDER, order.ProductionLine, order.OrderID, order.Weight, order.ProLineCode);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = "SQLDataProcess.AddOrUpdateWaitingOrder() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }
        #endregion

        #region 班组工作时间相关DB操作
        private void CreateTeamWorkTimeTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_TEAMWORKTIME_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateTeamWorkTimeTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateProlineEfficiency()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_PROLINEEFF_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateProlineEfficiency() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 取出最近一次班组的更新时间
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, DateTime> GetLatestTeamWorkTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                Dictionary<string, DateTime> retDic = new Dictionary<string, DateTime>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(LATEST_TEAMWORKTIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string strTime = reader["EndTime"].ToString();
                        string prolineCode = reader["ProLineCode"].ToString();
                        if (!string.IsNullOrEmpty(strTime) && !string.IsNullOrEmpty(prolineCode))
                        {
                            retDic.Add(prolineCode, DateTime.Parse(strTime).AddSeconds(1));
                        }
                    }
                    reader.Close();
                }
                return retDic;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = "SQLDataProcess.GetLatestTeamWorkTime() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }

        /// <summary>
        /// 添加或者更新班组的工作时间
        /// </summary>
        /// <param name="teamobj"></param>
        public void AddTeamWorkTime(TeamWorkTime teamobj)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                // 1. 取出这个团队在生产线上面的最近一次记录时间
                string query = string.Format(SELECT_TEAMWORKTIME_BY_TEAMID, teamobj.TeamID, teamobj.ProLineCode);
                bool canUpdate = false;
                DateTime? endTime = null;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        canUpdate = true;
                        string mixerID = reader["MixingerID"].ToString();
                        string exterID = reader["ExtruderID"].ToString();
                        string pckerID = reader["PackerID"].ToString();
                        string qulerID = reader["QualityID"].ToString();
                        if (mixerID != teamobj.MixingerID || exterID != teamobj.ExtruderID ||
                            pckerID != teamobj.PackerID || qulerID != teamobj.QualityID)
                            canUpdate = false;

                        endTime = DateTime.Parse(reader["EndTime"].ToString());
                        TimeSpan span = (TimeSpan)(teamobj.StartTime - (DateTime)endTime);
                        // 如果发现已经记录的时间和当前的时间差大于15分钟，我们认为这个班组已经换班
                        if (span.TotalMinutes > 15)
                        {
                            canUpdate = false;
                        }
                    }
                    reader.Close();
                }
                // 2. 更新这个班组的生产时间
                if (canUpdate && endTime != null)
                {
                    string update = string.Format(UPDATE_TEAMWORKTIME, teamobj.TeamID, endTime, teamobj.EndTime, teamobj.ProLineCode);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(update, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                // 添加一个新的班组生产时间
                else
                {
                    string insert = string.Format(INSERT_TEAMWORKTIME, teamobj.StartTime, teamobj.EndTime,
                        teamobj.TeamID, teamobj.MixingerID, teamobj.ExtruderID, teamobj.PackerID, teamobj.QualityID, teamobj.ProductionLine, teamobj.ProLineCode);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(insert, conn))
                    { 
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery(); 
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = "SQLDataProcess.AddTeamWorkTime() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }

        public void CollectionProlineStandby()
        {
            // 从后台获取生产线指定年份的效率数据数据
            Dictionary<int, List<UIData.ProductionLineStandby>> dic = this.GetProlineStandby();
            RecordProlineStandbyToDB(dic);
        }
        private Dictionary<int, List<UIData.ProductionLineStandby>> GetProlineStandby()
        {
            Dictionary<int, List<UIData.ProductionLineStandby>> retDic = new Dictionary<int, List<UIData.ProductionLineStandby>>();
            int startYear = StartDay.Year;
            int endYear = DateTime.Now.Year;
            DateTime startTime = StartDay;
            while(startYear <= endYear)
            {
                List<UIData.ProductionLineStandby> list = this.GetProductionLineStandby(startTime);
                retDic.Add(startYear, list);
                startTime = startTime.AddYears(1);
                startYear++;
            }
            return retDic;
        }
        private List<UIData.ProductionLineStandby> GetProductionLineStandby(DateTime year)
        {
            List<UIData.ProductionLineStandby> retList = new List<UIData.ProductionLineStandby>();
            // 1. 取出指定年内所有生产线
            List<string> prolines = this.GetAllProductionLineNames();
            // 2. 取出每条生产线的待机时间
            foreach (string proline in prolines)
            {
                UIData.ProductionLineStandby ps = this.GetProductionLineStandbyByYear(proline, year);
                retList.Add(ps);
            }
            return retList;
        }
        private void RecordProlineStandbyToDB(Dictionary<int, List<UIData.ProductionLineStandby>> dic)
        {
            foreach (KeyValuePair<int, List<UIData.ProductionLineStandby>> kv in dic)
            {
                foreach (UIData.ProductionLineStandby proline in kv.Value)
                {
                    if (this.IsExistProlineStandby(kv.Key, proline.ProductionLine))
                        this.UpdateProlineStandby(kv.Key, proline);
                    else
                        this.InsertProlineStandby(kv.Key, proline);
                }                
            }
        }
        private bool IsExistProlineStandby(int year, string proline)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                bool isExist = false;
                string query = string.Format(GET_PROLINESTANDBY, proline, year);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        isExist = true;
                    }
                    reader.Close();
                }
                return isExist;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.IsExistProlineStandby() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return true;
            }
        }
        private void InsertProlineStandby(int year, UIData.ProductionLineStandby obj)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (obj != null)
                {
                    string insert = string.Format(INSERT_PROLINESTANDBY, obj.ProductionLine, year,
                    obj.JanTime, obj.FebTime, obj.MarTime, obj.AprTime, obj.MayTime, obj.JunTime,
                    obj.JulTime, obj.AugTime, obj.SepTime, obj.OctTime, obj.NovTime, obj.DecTime,
                    obj.MaxTime, obj.MinTime);
                    using (cmd = new SqlCommand(insert, conn))
                    { cmd.ExecuteNonQuery(); }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.InsertProlineStandby() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void UpdateProlineStandby(int year, UIData.ProductionLineStandby obj)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (obj != null)
                {
                    string update = string.Format(UPDATE_PROLINESTANDBY, obj.ProductionLine, year,
                    obj.JanTime, obj.FebTime, obj.MarTime, obj.AprTime, obj.MayTime, obj.JunTime,
                    obj.JulTime, obj.AugTime, obj.SepTime, obj.OctTime, obj.NovTime, obj.DecTime,
                    obj.MaxTime, obj.MinTime);
                    using (cmd = new SqlCommand(update, conn))
                    { cmd.ExecuteNonQuery(); }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.UpdateProlineStandby() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        public void CollectionProlineEff()
        {
            // 从后台获取生产线指定年份的效率数据数据
            List<UIData.ProductionLineEfficiencyReport> list = this.GetProductionLineEfficiencyReport2();
            RecordProLineEffToDB(list);
        }

        public void CollectionProduceTask()
        {
            // 将当前的生产任务存储在数据库中
            List<UIData.ProductiveTaskReportTV> list = this.GetProductiveTaskReport();
            RecordProductiveTaskToDB(list);
        }

        private readonly static string GET_JCJPOWER_BY_TIMERANGE = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}'  AND [RecordTime] <= '{2}' " +
            "ORDER BY [RecordTime] DESC";
        public List<UIData.ProductiveTaskReportTV> GetProductiveTaskReport()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                List<UIData.ProductiveTaskReportTV> retList = new List<UIData.ProductiveTaskReportTV>();
                // 1. 获得当前所有生产线
                List<string> productionLines = this.GetAllProductionLineNames();
                // 2. 获得生产线上的在产信息
                foreach (string proLine in productionLines)
                {
                    // 获得这个生产线的生产效率
                    UIData.ProductiveTaskReportTV task = new UIData.ProductiveTaskReportTV();
                    task.ProductLine = proLine;
                    double weight;
                    double efficiency = GetProLineEfficiency(proLine, out weight);
                    UIData.OrderTask working = GetWorkingOrder(proLine, efficiency);
                    if (working != null)
                    {
                        task.ProductionEfficiency = Math.Round(efficiency, 0);
                        task.Weight = weight;
                        task.StartTime = working.StartTime;
                        task.CurrentOrderID = working.OrderID;
                        task.CurrentProductName = working.ProductionName;
                        task.CurrentWeight = working.Weight;
                        task.CurrentEstimateEndTime = working.EstimateEndTime;
                        DateTime updateTime = DateTime.Now;
                        string orderid = working.OrderID;
                        if (orderid.Length > 12)
                            orderid = orderid.Substring(2);
                        GetMemberInfo(proLine, working.UpdateTime, ref task);

                        string query = string.Format(GET_JCJPOWER_BY_TIMERANGE, proLine, working.StartTime, working.UpdateTime);
                        SqlConnection conn = new SqlConnection();
                        conn.ConnectionString = this.ConnString;
                        conn.Open();
                        using (cmd = new SqlCommand(query, conn))
                        {
                            double beginP = 0, endP = 0;
                            bool isFirstLine = true;
                            cmd.CommandTimeout = 0;
                            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                            while (reader.Read())
                            {
                                if (isFirstLine)
                                {
                                    endP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                                    if (endP > 0)
                                        isFirstLine = false;
                                }
                                else
                                {
                                    beginP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                                }
                            }
                            task.TotalPower = Math.Round(endP - beginP, 0);
                            if (task.Weight > 0)
                                task.TotalEnergy = Math.Round(task.TotalPower / task.Weight, 2);
                            reader.Close();
                        }
                    }
                    retList.Add(task);
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductiveTaskReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return null;
            }
        }
        private readonly static string GET_MEMBERID_BY_TIME = "SELECT * FROM [pret].[dbo].[TeamWorkTime] " +
            "WHERE [ProductionLine] = '{0}' AND [StartTime] <= '{1}' AND [EndTime] >= '{1}'";
        private void GetMemberInfo(string proline, DateTime workTime, ref UIData.ProductiveTaskReportTV obj)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 1. 获得这个时间段内的员工ID
                string query = string.Format(GET_MEMBERID_BY_TIME, proline, workTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        obj.TeamName = reader["TeamID"].ToString();
                        obj.MixingerName = reader["MixingerID"].ToString();
                        obj.ExtruderName = reader["ExtruderID"].ToString();
                        obj.PackerName = reader["PackerID"].ToString();
                        obj.QualityName = reader["QualityID"].ToString();
                    }
                    reader.Close();
                }
                if (!string.IsNullOrEmpty(obj.MixingerName))
                    obj.MixingerName = GetEmployeeName(obj.MixingerName);
                if (!string.IsNullOrEmpty(obj.ExtruderName))
                    obj.ExtruderName = GetEmployeeName(obj.ExtruderName);
                if (!string.IsNullOrEmpty(obj.PackerName))
                    obj.PackerName = GetEmployeeName(obj.PackerName);
                if (!string.IsNullOrEmpty(obj.QualityName))
                    obj.QualityName = GetEmployeeName(obj.QualityName);
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetMemberInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
            }
        }
        private readonly static string GET_EMPLOYEENAME_BY_ID = "SELECT [Name] FROM [ERPSystem].[dbo].[Employe] WHERE [ID] = '{0}'";
        private string GetEmployeeName(string employeeID)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                string employeeName = employeeID;
                string query = string.Format(GET_EMPLOYEENAME_BY_ID, employeeID);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        employeeName = reader["Name"].ToString();
                    }
                    reader.Close();
                }
                return employeeName;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetEmployeeName() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return employeeID;
            }
        }

        private readonly static string GET_JCJDETAIL_BY_PROLINE = "SELECT TOP 300 [RecordTime], [Bal1RealCount], [Bal2RealCount], [Bal3RealCount] FROM [pret].[dbo].[JCJDetail] WITH(NOLOCK)" +
            "WHERE [ProductionLine] = '{0}' " +
            "ORDER BY [RecordTime] DESC";
        private double GetProLineEfficiency(string proLine, out double weight)
        {
            weight = 0;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 获得最近一次订单的生产时间
                DateTime? startTime = null;
                DateTime? endTime = null;
                double efficiency = 0;
                double w1 = 0, w2 = 0, w3 = 0;
                string query = string.Format(GET_JCJDETAIL_BY_PROLINE, proLine);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    bool isStart = false;
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        double tmpw1 = double.Parse(reader["Bal1RealCount"].ToString());
                        double tmpw2 = double.Parse(reader["Bal2RealCount"].ToString());
                        double tmpw3 = double.Parse(reader["Bal3RealCount"].ToString());
                        if (isStart == false && (tmpw1 > 0 || tmpw2 > 0 || tmpw3 > 0))
                        {
                            startTime = DateTime.Parse(reader["RecordTime"].ToString());
                            w1 = tmpw1;
                            w2 = tmpw2;
                            w3 = tmpw3;
                            weight = w1 + w2 + w3;
                            isStart = true;
                        }
                        if (isStart == true)
                        {
                            if (w1 < tmpw1 || w2 < tmpw2 || w3 < tmpw3)
                                break;
                            else
                            {
                                endTime = DateTime.Parse(reader["RecordTime"].ToString());
                                if (tmpw1 == 0 && tmpw2 == 0 && tmpw3 == 0)
                                    break;
                                else
                                {
                                    w1 = tmpw1;
                                    w2 = tmpw2;
                                    w3 = tmpw3;
                                }
                            }
                        }
                    }
                    reader.Close();
                }
                weight -= (w1 + w2 + w3);
                if (weight > 0 && startTime != null && endTime != null)
                {
                    TimeSpan span = (TimeSpan)((DateTime)startTime - (DateTime)endTime);
                    if (span.TotalHours > 0.001)
                        efficiency = weight / span.TotalHours;
                }
                return efficiency;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProLineEfficiency() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return 0;
            }
        }

        private readonly static string GET_JCJORDER_BY_UPDATETIME = "SELECT [OrderID], [StartTime], [EndTime], [UpdateTime] FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProductionLine] = '{0}'" +
            "ORDER BY [UpdateTime] DESC";
        private UIData.OrderTask GetWorkingOrder(string proLine, double efficiency)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                string orderID = "";
                DateTime? startTime = null;
                DateTime updateTime = DateTime.Now;
                // 已用时间
                string query = string.Format(GET_JCJORDER_BY_UPDATETIME, proLine);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        orderID = reader["OrderID"].ToString();
                        // 如果EndTime为NULL,说明当前订单没有结束
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                            orderID = "";
                        startTime = DateTime.Parse(reader["StartTime"].ToString());
                        updateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                    }
                    reader.Close();
                }
                if (string.IsNullOrEmpty(orderID) || orderID == "MO0000000000")
                    return null;

                UIData.OrderTask order = new UIData.OrderTask();
                order.OrderID = orderID;
                order.StartTime = (DateTime)startTime;
                order.UpdateTime = updateTime;
                UIData.ERPProductionInfo proInfo = GetProductionInfo(orderID);
                if (proInfo != null)
                {
                    order.OrderID = proInfo.OrderID;
                    order.ProductionName = proInfo.ProductName;
                    order.Weight = proInfo.Count;
                }
                if (efficiency != 0 && order.Weight != 0)
                {
                    double totalHours = order.Weight / efficiency;
                    order.EstimateEndTime = ((DateTime)startTime).AddHours(totalHours);
                }
                return order;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetWorkingOrder() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return null;
            }
        }

        private readonly static string GET_ORDERINFO_FROM_ERP = "SELECT * FROM [ERPSystem].[dbo].[ProductionInfo] WHERE [OrderID] LIKE '%{0}'";
        private readonly static string GET_ORDERINFO_FROM_ERP2 = "SELECT * FROM [ERPSystem].[dbo].[ProductionInfo] " +
            "WHERE [OrderID] LIKE '%{0}' AND [ProductionLine] LIKE '{1}-%'";
        public UIData.ERPProductionInfo GetProductionInfo(string orderID, string productionline = "")
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            try
            {
                //ReConnectSQL();
                string query = "";
                UIData.ERPProductionInfo info = null;
                if (!string.IsNullOrEmpty(productionline))
                {
                    query = string.Format(GET_ORDERINFO_FROM_ERP2, orderID, productionline);
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            info = new UIData.ERPProductionInfo();
                            info.OrderID = reader["OrderID"].ToString();
                            info.ProductName = reader["ProductName"].ToString();
                            info.BatchID = reader["BatchID"].ToString();
                            info.StockID = reader["StockID"].ToString();
                            info.Count = double.Parse(reader["Count"].ToString());
                            info.CurrCount = double.Parse(reader["CurrCount"].ToString());
                        }
                        reader.Close();
                    }
                }
                if (info != null)
                    return info;

                conn.Open();
                query = string.Format(GET_ORDERINFO_FROM_ERP, orderID);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        info = new UIData.ERPProductionInfo();
                        info.OrderID = reader["OrderID"].ToString();
                        info.ProductName = reader["ProductName"].ToString();
                        info.BatchID = reader["BatchID"].ToString();
                        info.StockID = reader["StockID"].ToString();
                        info.Count = double.Parse(reader["Count"].ToString());
                        info.CurrCount = double.Parse(reader["CurrCount"].ToString());
                    }
                    reader.Close();
                }
                return info;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return null;
            }
        }

        private List<UIData.ProductionLineEfficiencyReport> GetProductionLineEfficiencyReport2()
        {
            DateTime year = DateTime.Parse(DateTime.Now.ToString("yyyy-MM"));
            try
            {
                ////ReConnectSQL();
                List<UIData.ProductionLineEfficiencyReport> retList = new List<UIData.ProductionLineEfficiencyReport>();
                // 1. 取出指定年内所有生产线的订单号
                List<string> proList = GetAllProductionLineNames();
                foreach (string proline in proList)
                {
                    UIData.ProductionLineEfficiencyReport obj = GetProductionEfficiency(proline, year);
                    UIData.ProductionLineStandby ps = GetProductionLineStandbyByYear(proline, year);
                    if (obj == null || ps == null)
                        continue;
                    MergerProductionLineEfficiency(ref obj, ps);
                    if (obj != null)
                    {
                        retList.Add(obj);
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetUIData.ProductionLineEfficiencyReport2() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return null;
            }
        }
        private UIData.ProductionLineEfficiencyReport GetProductionEfficiency(string proline, DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double maxEffic = double.MinValue;
                double minEffic = double.MaxValue;
                UIData.ProductionLineEfficiencyReport obj = new UIData.ProductionLineEfficiencyReport();
                obj.ProductionLine = proline;
                for (int i = 0; i < 12; i++)
                {
                    string month = String.Format("{0}/{1}/01", year.Year, i + 1);
                    DateTime startM = DateTime.Parse(month);
                    DateTime endM = startM.AddMonths(1);
                    string query = string.Format(GET_PROLINESTATUS_BY_PROLINE, proline, startM, endM);
                    double output = 0, hours = 0, efficiency = 0;
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            if (reader["Weight"] != null)
                                output = double.Parse(reader["Weight"].ToString());
                            if (reader["TotalTime"] != null)
                                hours = Math.Round(double.Parse(reader["TotalTime"].ToString()) / 60, 0);
                        }
                        reader.Close();
                    }
                    obj.Hours += hours;
                    obj.Weight += output;
                    if (hours > 0)
                    {
                        efficiency = Math.Round(output / hours, 0);
                        maxEffic = maxEffic < efficiency ? efficiency : maxEffic;
                        if (efficiency > 0)
                            minEffic = minEffic > efficiency ? efficiency : minEffic;
                    }
                    switch (i)
                    {
                        case 0:
                            obj.JanEfficiency = efficiency;
                            obj.JanHours = hours;
                            obj.JanWeight = output;
                            break;
                        case 1:
                            obj.FebEfficiency = efficiency;
                            obj.FebHours = hours;
                            obj.FebWeight = output;
                            break;
                        case 2:
                            obj.MarEfficiency = efficiency;
                            obj.MarHours = hours;
                            obj.MarWeight = output;
                            break;
                        case 3:
                            obj.AprEfficiency = efficiency;
                            obj.AprHours = hours;
                            obj.AprWeight = output;
                            break;
                        case 4:
                            obj.MayEfficiency = efficiency;
                            obj.MayHours = hours;
                            obj.MayWeight = output;
                            break;
                        case 5:
                            obj.JunEfficiency = efficiency;
                            obj.JunHours = hours;
                            obj.JunWeight = output;
                            break;
                        case 6:
                            obj.JulEfficiency = efficiency;
                            obj.JulHours = hours;
                            obj.JulWeight = output;
                            break;
                        case 7:
                            obj.AugEfficiency = efficiency;
                            obj.AugHours = hours;
                            obj.AugWeight = output;
                            break;
                        case 8:
                            obj.SepEfficiency = efficiency;
                            obj.SepHours = hours;
                            obj.SepWeight = output;
                            break;
                        case 9:
                            obj.OctEfficiency = efficiency;
                            obj.OctHours = hours;
                            obj.OctWeight = output;
                            break;
                        case 10:
                            obj.NovEfficiency = efficiency;
                            obj.NovHours = hours;
                            obj.NovWeight = output;
                            break;
                        case 11:
                            obj.DecEfficiency = efficiency;
                            obj.DecHours = hours;
                            obj.DecWeight = output;
                            break;
                    }
                }
                if (maxEffic != double.MinValue)
                    obj.MaxEfficiency = Math.Round(maxEffic, 0);
                if (minEffic != double.MaxValue)
                    obj.MinEfficiency = Math.Round(minEffic, 0);
                if (obj.Hours > 0)
                    obj.AvgEfficiency = Math.Round(obj.Weight / obj.Hours, 0);
                return obj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionEfficiency() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return null;
            }
        }
        private UIData.ProductionLineStandby GetProductionLineStandbyByYear(string proline, DateTime year)
        {
            double maxhours = double.MinValue;
            double minhours = double.MaxValue;
            UIData.ProductionLineStandby obj = new UIData.ProductionLineStandby();
            obj.ProductionLine = proline;
            string startM = String.Format("{0}/01/01", year.Year);
            DateTime startTime = DateTime.Parse(startM);
            while (startTime < year.AddYears(1))
            {
                double hours = GetProductionLineStandbyByYear(proline, startTime, startTime.AddMonths(1).AddSeconds(-1));
                maxhours = maxhours > hours ? maxhours : hours;
                if (hours > 0.001)
                    minhours = minhours < hours ? minhours : hours;
                switch (startTime.Month)
                {
                    case 1:
                        obj.JanTime = hours;
                        break;
                    case 2:
                        obj.FebTime = hours;
                        break;
                    case 3:
                        obj.MarTime = hours;
                        break;
                    case 4:
                        obj.AprTime = hours;
                        break;
                    case 5:
                        obj.MayTime = hours;
                        break;
                    case 6:
                        obj.JunTime = hours;
                        break;
                    case 7:
                        obj.JulTime = hours;
                        break;
                    case 8:
                        obj.AugTime = hours;
                        break;
                    case 9:
                        obj.SepTime = hours;
                        break;
                    case 10:
                        obj.OctTime = hours;
                        break;
                    case 11:
                        obj.NovTime = hours;
                        break;
                    case 12:
                        obj.DecTime = hours;
                        break;
                }
                if (maxhours != double.MinValue)
                    obj.MaxTime = maxhours;
                if (minhours != double.MaxValue)
                    obj.MinTime = minhours;
                startTime = startTime.AddMonths(1);
            }
            return obj;
        }
        private double GetProductionLineStandbyByYear(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double hours = 0;
                double sumMins = 0;
                string query = string.Format(GET_JCJCURRENT_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    // 由于数据是按照3分钟的间隔记录的，所以按照3分钟进行叠加
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        sumMins += 3;
                    }
                    reader.Close();
                }
                hours = sumMins / 60;
                return hours;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionLineStandbyByYear() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                ////ReConnectSQL();
                return 0;
            }
        }
        private void MergerProductionLineEfficiency(ref UIData.ProductionLineEfficiencyReport obj, UIData.ProductionLineStandby ps)
        {
            obj.JanHours += ps.JanTime;
            obj.JanHours = Math.Round(obj.JanHours, 0);
            if (obj.JanHours > 0)
                obj.JanEfficiency = Math.Round(obj.JanWeight / obj.JanHours, 0);
            obj.FebHours += ps.FebTime;
            obj.FebHours = Math.Round(obj.FebHours, 0);
            if (obj.FebHours > 0)
                obj.FebEfficiency = Math.Round(obj.FebWeight / obj.FebHours, 0);
            obj.MarHours += ps.MarTime;
            obj.MarHours = Math.Round(obj.MarHours, 0);
            if (obj.MarHours > 0)
                obj.MarEfficiency = Math.Round(obj.MarWeight / obj.MarHours, 0);
            obj.AprHours += ps.AprTime;
            obj.AprHours = Math.Round(obj.AprHours, 0);
            if (obj.AprHours > 0)
                obj.AprEfficiency = Math.Round(obj.AprWeight / obj.AprHours, 0);
            obj.MayHours += ps.MayTime;
            obj.MayHours = Math.Round(obj.MayHours, 0);
            if (obj.MayHours > 0)
                obj.MayEfficiency = Math.Round(obj.MayWeight / obj.MayHours, 0);
            obj.JunHours += ps.JunTime;
            obj.JunHours = Math.Round(obj.JunHours, 0);
            if (obj.JunHours > 0)
                obj.JunEfficiency = Math.Round(obj.JunWeight / obj.JunHours, 0);
            obj.JulHours += ps.JulTime;
            obj.JulHours = Math.Round(obj.JulHours, 0);
            if (obj.JulHours > 0)
                obj.JulEfficiency = Math.Round(obj.JulWeight / obj.JulHours, 0);
            obj.AugHours += ps.AugTime;
            obj.AugHours = Math.Round(obj.AugHours, 0);
            if (obj.AugHours > 0)
                obj.AugEfficiency = Math.Round(obj.AugWeight / obj.AugHours, 0);
            obj.SepHours += ps.SepTime;
            obj.SepHours = Math.Round(obj.SepHours, 0);
            if (obj.SepHours > 0)
                obj.SepEfficiency = Math.Round(obj.SepWeight / obj.SepHours, 0);
            obj.OctHours += ps.OctTime;
            obj.OctHours = Math.Round(obj.OctHours, 0);
            if (obj.OctHours > 0)
                obj.OctEfficiency = Math.Round(obj.OctWeight / obj.OctHours, 0);
            obj.NovHours += ps.NovTime;
            obj.NovHours = Math.Round(obj.NovHours, 0);
            if (obj.NovHours > 0)
                obj.NovEfficiency = Math.Round(obj.NovWeight / obj.NovHours, 0);
            obj.DecHours += ps.DecTime;
            obj.DecHours = Math.Round(obj.DecHours, 0);
            if (obj.DecHours > 0)
                obj.DecEfficiency = Math.Round(obj.DecWeight / obj.DecHours, 0);
        }
        private List<string> GetAllProductionLineNames()
        {
            string[] deviceList = PRET.PRETService.Properties.Resources.ProlineList.Split(new char[] { ',' });
            List<string> lists = deviceList.ToList();
            return lists;
        }
        private void RecordProLineEffToDB(List<UIData.ProductionLineEfficiencyReport> list)
        {
            int year = DateTime.Now.Year;
            if (list == null || list.Count == 0)
                return;
            else
            {
                foreach (UIData.ProductionLineEfficiencyReport item in list)
                {
                    if (this.IsExistProlineEff(item.ProductionLine, year))
                        this.UpdateProlineEff(item, year);
                    else
                        this.InsertProlineEff(item, year);
                }
            }
        }
        private bool IsExistProlineEff(string proline, int year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                bool isExist = false;
                string query = string.Format(GET_PROLINEEFF, proline, year);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        isExist = true;
                    }
                    reader.Close();
                }
                return isExist;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.IsExistProlineEff() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return true;
            }
        }
        private void InsertProlineEff(UIData.ProductionLineEfficiencyReport item, int year)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (item != null)
                {
                    string insert = string.Format(INSERT_PROLINEEFF, item.ProductionLine, year,
                    item.JanEfficiency, item.JanHours, item.JanWeight,
                    item.FebEfficiency, item.FebHours, item.FebWeight,
                    item.MarEfficiency, item.MarHours, item.MarWeight,
                    item.AprEfficiency, item.AprHours, item.AprWeight,
                    item.MayEfficiency, item.MayHours, item.MayWeight,
                    item.JunEfficiency, item.JunHours, item.JunWeight,
                    item.JulEfficiency, item.JulHours, item.JulWeight,
                    item.AugEfficiency, item.AugHours, item.AugWeight,
                    item.SepEfficiency, item.SepHours, item.SepWeight,
                    item.OctEfficiency, item.OctHours, item.OctWeight,
                    item.NovEfficiency, item.NovHours, item.NovWeight,
                    item.DecEfficiency, item.DecHours, item.DecWeight,
                    item.AvgEfficiency, item.MaxEfficiency, item.MinEfficiency, item.EfficiencyQuota,
                    item.Weight, item.Hours, item.CurrEfficiency);
                    using (cmd = new SqlCommand(insert, conn))
                    { cmd.ExecuteNonQuery(); }
                }                
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.InsertProlineEff() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void UpdateProlineEff(UIData.ProductionLineEfficiencyReport item, int year)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (item != null)
                {
                    string update = string.Format(UPDATE_PROLINEEFF, item.ProductionLine, year,
                        item.JanEfficiency, item.JanHours, item.JanWeight,
                        item.FebEfficiency, item.FebHours, item.FebWeight,
                        item.MarEfficiency, item.MarHours, item.MarWeight,
                        item.AprEfficiency, item.AprHours, item.AprWeight,
                        item.MayEfficiency, item.MayHours, item.MayWeight,
                        item.JunEfficiency, item.JunHours, item.JunWeight,
                        item.JulEfficiency, item.JulHours, item.JulWeight,
                        item.AugEfficiency, item.AugHours, item.AugWeight,
                        item.SepEfficiency, item.SepHours, item.SepWeight,
                        item.OctEfficiency, item.OctHours, item.OctWeight,
                        item.NovEfficiency, item.NovHours, item.NovWeight,
                        item.DecEfficiency, item.DecHours, item.DecWeight,
                        item.AvgEfficiency, item.MaxEfficiency, item.MinEfficiency, item.EfficiencyQuota,
                        item.Weight, item.Hours, item.CurrEfficiency);
                    using (cmd = new SqlCommand(update, conn))
                    { cmd.ExecuteNonQuery(); }
                }                
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.UpdateProlineEff() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }



        private void RecordProductiveTaskToDB(List<UIData.ProductiveTaskReportTV> list)
        {
            int year = DateTime.Now.Year;
            if (list == null || list.Count == 0)
                return;
            else
            {
                foreach (UIData.ProductiveTaskReportTV item in list)
                {
                    if (this.IsExistProductiveTask(item.ProductLine))
                        this.UpdateProductiveTask(item);
                    else
                        this.InsertProductiveTask(item);
                }
            }
        }
        private bool IsExistProductiveTask(string proline)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                bool isExist = false;
                string query = string.Format(SELECT_A_PRODUCE_TASK, proline);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        isExist = true;
                    }
                    reader.Close();
                }
                return isExist;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.IsExistProductiveTask() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return true;
            }
        }
        private void InsertProductiveTask(UIData.ProductiveTaskReportTV item)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (item != null)
                {
                    string s = (item.StartTime == null) ? "NULL" : "'" + item.StartTime.ToString() + "'";
                    string e = (item.CurrentEstimateEndTime == null) ? "NULL" : "'" + item.CurrentEstimateEndTime.ToString() + "'";
                    string insert = string.Format(INSERT_PRODUCE_TASK, item.ProductLine, 
                        item.CurrentProductName, item.CurrentWeight, s,
                        e, item.CurrentOrderID, item.TeamName,
                    item.MixingerName, item.ExtruderName, item.PackerName,
                    item.QualityName, item.ProductionEfficiency, item.Weight,
                    item.TotalPower, item.TotalEnergy);
                    using (cmd = new SqlCommand(insert, conn))
                    { cmd.ExecuteNonQuery(); }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.InsertProductiveTask() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        private void UpdateProductiveTask(UIData.ProductiveTaskReportTV item)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (item != null)
                {
                    string s = (item.StartTime == null) ? "NULL" : "'"+item.StartTime.ToString()+"'";
                    string e = (item.CurrentEstimateEndTime == null) ? "NULL" : "'" + item.CurrentEstimateEndTime.ToString() + "'";
                    string update = string.Format(UPDATE_PRODUCE_TASK, item.ProductLine, 
                        item.CurrentProductName, item.CurrentWeight, s,
                        e, item.CurrentOrderID, item.TeamName,
                        item.MixingerName, item.ExtruderName, item.PackerName,
                        item.QualityName, item.ProductionEfficiency, item.Weight,
                        item.TotalPower, item.TotalEnergy);
                    using (cmd = new SqlCommand(update, conn))
                    { cmd.ExecuteNonQuery(); }
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.UpdateProductiveTask() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 高低柜相关DB操作
        // 创建高低柜数据表
        public void CreateHigLowTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_HIGHLOW_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateHigLowTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        // 创建高低柜电压数据表
        public void CreateHigLowVoltageTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_HIGHLOWVOLTAGE_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateHigLowVoltageTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        // 创建湿度、温度数据表
        public void CreateHumidityTemperatureTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_HUMIDITY_TEMPERATURE_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //string msg = "SQLDataProcess.CreateJHJInfoTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }

        public void AddHighLowInfo(HighLowInfo info)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                if (info == null)
                    return;

                // 找到当天已有的数据记录
                HighLowInfo existInfo = null;
                DateTime startTime = info.Date.Date;
                DateTime endTime = info.Date.AddDays(1).Date;
                string query = string.Format(GET_HIGHLOW_BY_DEVICE_TIME, info.DeviceID, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        existInfo = new HighLowInfo();
                        existInfo.DeviceID = reader["DeviceID"].ToString();
                        existInfo.Date = DateTime.Parse(reader["Date"].ToString());
                        existInfo.MaxCurrent = float.Parse(reader["MaxCurrent"].ToString());
                        existInfo.MinCurrent = float.Parse(reader["MinCurrent"].ToString());
                        existInfo.MaxVoltage = float.Parse(reader["MaxVoltage"].ToString());
                        existInfo.MinVoltage = float.Parse(reader["MinVoltage"].ToString());
                        existInfo.MaxLoad = float.Parse(reader["MaxLoad"].ToString());
                        existInfo.MinLoad = float.Parse(reader["MinLoad"].ToString());
                        existInfo.PowerFactor = float.Parse(reader["PowerFactor"].ToString());
                    }
                    reader.Close();
                }

                // 如果存在当天数据那么更新这个数据, 否则添加新的数据
                if(existInfo != null)
                {
                    info.MaxCurrent = info.MaxCurrent > existInfo.MaxCurrent ? info.MaxCurrent : existInfo.MaxCurrent;
                    if (info.MinCurrent == 0)
                        info.MinCurrent = existInfo.MinCurrent;
                    else
                        info.MinCurrent = info.MinCurrent < existInfo.MinCurrent ? info.MinCurrent : existInfo.MinCurrent;
                    info.MaxVoltage = info.MaxVoltage > existInfo.MaxVoltage ? info.MaxVoltage : existInfo.MaxVoltage;
                    if (info.MinVoltage == 0)
                        info.MinVoltage = existInfo.MinVoltage;
                    else
                        info.MinVoltage = info.MinVoltage < existInfo.MinVoltage ? info.MinVoltage : existInfo.MinVoltage;
                    info.MaxLoad = info.MaxLoad > existInfo.MaxLoad ? info.MaxLoad : existInfo.MaxLoad;
                    if (info.MinLoad == 0)
                        info.MinLoad = existInfo.MinLoad;
                    else
                        info.MinLoad = info.MinLoad < existInfo.MinLoad ? info.MinLoad : existInfo.MinLoad;
                    info.PowerFactor = info.PowerFactor > existInfo.PowerFactor ? info.PowerFactor : existInfo.PowerFactor;
                    query = string.Format(UPDATE_HIGHLOW, info.DeviceID, info.Date, info.MaxCurrent, info.MinCurrent,
                        info.MaxVoltage, info.MinVoltage, info.MaxLoad, info.MinLoad, info.PowerFactor);
                }
                else
                {
                    query = string.Format(INSERT_HIGHLOW, info.DeviceID, info.Date, info.MaxCurrent, info.MinCurrent,
                        info.MaxVoltage, info.MinVoltage, info.MaxLoad, info.MinLoad, info.PowerFactor);
                }
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddHighLowInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }

        public void AddHighLowVoltage(HighLowVoltage info)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (info == null)
                    return;
                string query = string.Format(INSERT_HIGHLOWVOLTAGE, info.DeviceID, info.UpdateTime, info.Degree);
                using (cmd = new SqlCommand(query, conn))
                { cmd.ExecuteNonQuery(); }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddHighLowVoltage() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        public void AddHumTem(HumidityTemperature info)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                if (info == null)
                    return;

                // 找到当天已有的数据记录
                HumidityTemperature existInfo = null;
                DateTime startTime = info.Date.Date;
                DateTime endTime = info.Date.AddDays(1).Date.AddSeconds(-1);
                string query = string.Format(GET_HUMIDITY_TEMPERATURE_BY_TIME, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        existInfo = new HumidityTemperature();
                        existInfo.Date = DateTime.Parse(reader["Date"].ToString());
                        existInfo.Humidity = float.Parse(reader["MaxHumidity"].ToString());
                        existInfo.Temperature = float.Parse(reader["MaxTemperature"].ToString());
                    }
                    reader.Close();
                }

                // 如果存在当天数据那么更新这个数据, 否则添加新的数据
                if (existInfo != null)
                {
                    info.Humidity = info.Humidity > existInfo.Humidity ? info.Humidity : existInfo.Humidity;
                    info.Temperature = info.Temperature > existInfo.Temperature ? info.Temperature : existInfo.Temperature;
                    query = string.Format(UPDATE_HUMIDITY_TEMPERATURE, info.Date, info.Humidity, info.Temperature);
                }
                else
                {
                    query = string.Format(INSERT_HUMIDITY_TEMPERATURE, info.Date, info.Humidity, info.Temperature);
                }
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.AddHumTem() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
            }
        }

        public Dictionary<string, DateTime> GetLatestHighLowTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                Dictionary<string, DateTime> retDic = new Dictionary<string, DateTime>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(LATEST_HIGHLOWTIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string strDate = reader["Date"].ToString();
                        string deviceID = reader["DeviceID"].ToString();
                        if (!string.IsNullOrEmpty(strDate) && !string.IsNullOrEmpty(deviceID))
                        {
                            DateTime latest = DateTime.Parse(strDate);
                            if (!retDic.ContainsKey(deviceID))
                            {
                                retDic.Add(deviceID, latest.AddSeconds(1));
                            }
                        }
                    }
                    reader.Close();
                }
                return retDic;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestHighLowTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }

        public Dictionary<string, DateTime> GetLatestHighLowVoltageTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                Dictionary<string, DateTime> retDic = new Dictionary<string, DateTime>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(LATEST_HIGHLOWVOLTAGETIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string strDate = reader["UpdateTime"].ToString();
                        string deviceID = reader["DeviceID"].ToString();
                        if (!string.IsNullOrEmpty(strDate) && !string.IsNullOrEmpty(deviceID))
                        {
                            DateTime latest = DateTime.Parse(strDate);
                            if (!retDic.ContainsKey(deviceID))
                            {
                                retDic.Add(deviceID, latest.AddSeconds(1));
                            }
                        }
                    }
                    reader.Close();
                }
                return retDic;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestHighLowVoltageTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }

        public DateTime GetLatestHumTemTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                DateTime date = StartDay;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(LATEST_HUMTEMTIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader["Date"].ToString()))
                            date = DateTime.Parse(reader["Date"].ToString());
                    }
                    reader.Close();
                }
                return date.AddSeconds(1);
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestHumTemTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return DateTime.Now.Date;
            }
        }
        #endregion

        #region 滑石母粒机相关DB操作
        private void CreateHSMLJTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_HSMLJ_TABLE, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //string msg = "SQLDataProcess.CreateHSMLJTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        public DateTime GetLatestHSMLJTime()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                DateTime startTime = StartDay;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(LATEST_HSMLJTAILTIME, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader["UpdateTime"].ToString()))
                            startTime = DateTime.Parse(reader["UpdateTime"].ToString());
                    }
                    reader.Close();
                }
                return startTime.AddSeconds(1);
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestHSMLJTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return StartDay;
            }
        }

        public void AddHSMLJ(HSMLJInfo newInfo)
        {
            try
            {
                HSMLJInfo existObj = this.GetLatestHSMLJInfo();
                // 如果数据库中找不到指定订单的挤出机数据，那么添加数据到JCJInfo表格中
                if (existObj == null && newInfo.StartTime != null)
                {
                    AddHSMLJInfoInDB(newInfo);
                }
                // 如果有存在的订单，更新这个订单
                else if (existObj != null)
                {
                    string update = "";
                    // 如果记录中没有结束时间，但是新的数据有没有开始时间，那么将原来的记录像更新，抛弃掉新的记录像
                    if (newInfo.StartTime == null)
                    {
                        update = string.Format(UPDATE_HSMLJ_ENDTIME, existObj.StartTime, existObj.UpdateTime, existObj.UpdateTime,
                            existObj.MainA, existObj.MainB, existObj.ExtraA, existObj.ExtraB, existObj.EndPower);
                    }
                    else if(newInfo.StartTime != null && newInfo.EndTime != null)
                    {
                        update = string.Format(UPDATE_HSMLJ_ENDTIME, existObj.StartTime, newInfo.EndTime, newInfo.UpdateTime,
                            newInfo.MainA, newInfo.MainB, newInfo.ExtraA, newInfo.ExtraB, newInfo.EndPower);
                    }
                    else
                    {
                        update = string.Format(UPDATE_HSMLJ_NOENDTIME, existObj.StartTime, newInfo.UpdateTime,
                            newInfo.MainA, newInfo.MainB, newInfo.ExtraA, newInfo.ExtraB, newInfo.EndPower);
                    }
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(update, conn))
                    {
                        cmd.CommandTimeout = 0; 
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddHSMLJ() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
        }
        private HSMLJInfo GetLatestHSMLJInfo()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                HSMLJInfo obj = null;
                // 先取出未完成时间的订单数据
                string query = string.Format(GET_HSMLJ_WITHOUT_ENDTIME);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        obj = new HSMLJInfo();
                        obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        obj.EndTime = null;
                        obj.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        obj.MainA = float.Parse(reader["MainA"].ToString());
                        obj.MainB = float.Parse(reader["MainB"].ToString());
                        obj.ExtraA = float.Parse(reader["ExtraA"].ToString());
                        obj.ExtraB = float.Parse(reader["ExtraB"].ToString());
                        obj.StartPower = float.Parse(reader["StartPower"].ToString());
                        obj.EndPower = float.Parse(reader["EndPower"].ToString());
                    }
                    reader.Close();
                }
                return obj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetLatestHSMLJInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private void AddHSMLJInfoInDB(HSMLJInfo obj)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                string start = "null";
                if (obj.StartTime != null)
                    start = "'" + obj.StartTime.ToString() + "'";
                string end = "null";
                if (obj.EndTime != null)
                    end = "'" + obj.EndTime.ToString() + "'";
                string insert = string.Format(INSERT_HSMLJINFO, start, end, obj.UpdateTime,
                    obj.MainA, obj.MainB, obj.ExtraA, obj.ExtraB, obj.StartPower, obj.EndPower);
                using (SqlCommand cmd = new SqlCommand(insert, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.AddHSMLJInfoInDB() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        private Dictionary<string, DateTime> MergerLatestRecordTime(Dictionary<string, DateTime> dic1, Dictionary<string, DateTime> dic2)
        {
            if(dic1 == null || dic1.Count == 0)
                return dic2;
            if (dic2 == null || dic2.Count == 0)
                return dic1;

            Dictionary<string, DateTime> retDic = new Dictionary<string, DateTime>();
            foreach(KeyValuePair<string, DateTime> item in dic1)
            {
                if(dic2.ContainsKey(item.Key))
                {
                    if (DateTime.Compare(item.Value, dic2[item.Key]) > 0)
                        retDic.Add(item.Key, item.Value);
                    else
                        retDic.Add(item.Key, dic2[item.Key]);
                }
                else
                {
                    retDic.Add(item.Key, item.Value);
                }
            }
            return retDic;
        }

        #region 返回给ERP的订单数据
        public void UpdateOrderFeedBackTable()
        {
            // 本功能只收集已经完成的订单号!

            // 1. 获得当前已经收集到的订单数据
            List<OrderFeedback> sysList = GetOrderFeedBackFromJCJ();
            // 2. 获得当前已经更新过的订单数据
            List<OrderFeedback> erpList = GetOrderFeedBackFromERP(sysList);
            // 3. 剔除掉相同的OrderFeedback对象
            List<OrderFeedback> orderList = MergeOrderFeedBack(sysList, erpList);
            // 4. 将最后的OrderFeedback List更新到OrderFeedBack table中
            UpdateOrderFeedBackList(orderList);
        }

        private List<OrderFeedback> GetOrderFeedBackFromJCJ()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                List<OrderFeedback> orderList = new List<OrderFeedback>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_JCJINFO, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string orderID = reader["OrderID"].ToString();
                        if (string.IsNullOrEmpty(orderID) || orderID == "MO0000000000")
                            continue;
                        OrderFeedback order = new OrderFeedback();
                        order.OrderID = reader["OrderID"].ToString();
                        order.ProLineCode = reader["ProLineCode"].ToString();
                        order.ProductionLine = reader["ProductionLine"].ToString();
                        order.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        order.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                        orderList.Add(order);
                    }
                    reader.Close();
                }

                // 能耗比，生产效率，溶值等数据
                for (int i = 0; i < orderList.Count; i++ )
                {
                    // 取出这段时间内的班组信息
                    orderList[i].TeamList = GetTeamInfoByTime(orderList[i].ProLineCode, orderList[i].StartTime, orderList[i].EndTime);
                    double rz = 0, beginP = 0, endP = 0;
                    bool isFirstLine = true;
                    // 从ERP取出当前已经生产的重量
                    double weight = this.GetProLineWeight(orderList[i].ProductionLine, orderList[i].StartTime, orderList[i].EndTime);
                    string query = string.Format(GET_JCJDETAILINFO_BY_ORDER_TIME, orderList[i].ProductionLine, orderList[i].StartTime, orderList[i].EndTime);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            if (isFirstLine)
                            {
                                rz = double.Parse(reader["RealRongZhi"].ToString());
                                endP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                                if (endP != 0)
                                    isFirstLine = false;
                            }
                            else
                            {
                                beginP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                            }
                        }
                        reader.Close();
                    }
                    TimeSpan timeSpan = (TimeSpan)(orderList[i].EndTime - orderList[i].StartTime);
                    double hours = timeSpan.TotalHours;
                    if (hours != 0)
                        orderList[i].Efficiency = Math.Round(weight / hours, 0);
                    if (weight != 0)
                    {
                        double powerTotal = endP - beginP;
                        orderList[i].PowerRate = Math.Round(powerTotal / weight, 2);
                    }
                    if (rz != 0)
                        orderList[i].RongZhi = Math.Round(rz, 0);
                }
                return orderList;
            }
            catch(SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOrderFeedBackFromJCJ() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private List<TeamInfo> GetTeamInfoByTime(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                HashSet<string> teamHash = new HashSet<string>();
                List<TeamInfo> retList = new List<TeamInfo>();
                string query = string.Format(SELECT_TEAMWORKTIME_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        if (teamHash.Contains(reader["TeamID"].ToString()))
                            continue;
                        else
                        {
                            TeamInfo team = new TeamInfo();
                            team.TeamID = reader["TeamID"].ToString();
                            team.MixingerID = reader["MixingerID"].ToString();
                            team.ExtruderID = reader["ExtruderID"].ToString();
                            team.PackerID = reader["PackerID"].ToString();
                            team.QualityID = reader["QualityID"].ToString();
                            retList.Add(team);
                            teamHash.Add(team.TeamID);
                        }                        
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamInfoByTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private List<OrderFeedback> GetOrderFeedBackFromERP(List<OrderFeedback> sysList)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                if (sysList == null || sysList.Count == 0)
                    return null;
                List<OrderFeedback> orderList = new List<OrderFeedback>();
                foreach(OrderFeedback sysInfo in sysList)
                {
                    string query = string.Format(GET_ORDERFEEDBACK_FROM_ERP, sysInfo.OrderID, sysInfo.ProductionLine, sysInfo.StartTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        OrderFeedback order = null;
                        while (reader.Read())
                        {
                            if (order == null)
                            {
                                order = new OrderFeedback();
                                order.OrderID = reader["OrderID"].ToString();
                                order.PowerRate = double.Parse(reader["PowerRate"].ToString());
                                order.Efficiency = double.Parse(reader["Efficiency"].ToString());
                                order.RongZhi = double.Parse(reader["RongZhi"].ToString());
                                order.ProductionLine = reader["ProductionLine"].ToString();
                                order.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                                order.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                                order.TeamList = new List<TeamInfo>();
                            }
                            TeamInfo team = new TeamInfo();
                            team.TeamID = reader["TeamID"].ToString();
                            team.ExtruderID = reader["ExtruderID"].ToString();
                            team.MixingerID = reader["MixingerID"].ToString();
                            team.PackerID = reader["PackerID"].ToString();
                            team.QualityID = reader["QualityID"].ToString();
                            order.TeamList.Add(team);
                        }
                        reader.Close();
                        if (order != null)
                            orderList.Add(order);
                    }
                }                
                return orderList;
            }
            catch (SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOrderFeedBackFromERP() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private List<OrderFeedback> MergeOrderFeedBack(List<OrderFeedback> sysList, List<OrderFeedback> erpList)
        {
            try
            {
                if (erpList == null || erpList.Count == 0)
                    return sysList;

                List<OrderFeedback> orderList = new List<OrderFeedback>();
                foreach(OrderFeedback item in sysList)
                {
                    bool isExist = false;
                    for(int i = 0; i < erpList.Count; i++)
                    {
                        if(item.OrderID == erpList[i].OrderID)
                        {
                            // 如果两个值相等，则跳过这个订单
                            if (OrderFeedback.Compare(item, erpList[i]))
                            {
                                isExist = true;
                                erpList.RemoveAt(i);
                                break;
                            }
                        }
                    }
                    // 保存新的订单反馈
                    if (!isExist)
                        orderList.Add(item);
                }
                return orderList;
            }
            catch (SqlException ex)
            {
                string msg = string.Format("SQLDataProcess.MergeOrderFeedBack() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        private void UpdateOrderFeedBackList(List<OrderFeedback> orderList)
        {
            SqlCommand cmd = null;
            try
            {
                foreach(OrderFeedback item in orderList)
                {
                    // 1. 先删除老的订单
                    string query = string.Format(DELETE_ORDERFEEDBACK, item.OrderID, item.ProductionLine, item.StartTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();

                    // 2. 添加新的订单
                    if(item.TeamList != null || item.TeamList.Count > 0)
                    {
                        foreach (TeamInfo team in item.TeamList)
                        {
                            query = string.Format(INSERT_ORDERFEEDBACK, item.OrderID, item.PowerRate, item.Efficiency, item.RongZhi, team.TeamID, team.MixingerID, team.ExtruderID, team.PackerID, team.QualityID, item.ProductionLine, item.StartTime, item.EndTime);
                            conn = new SqlConnection();
                            conn.ConnectionString = this.ConnString;
                            conn.Open();
                            using (cmd = new SqlCommand(query, conn))
                            { 
                                cmd.CommandTimeout = 0;
                                cmd.ExecuteNonQuery(); 
                            }
                            conn.Close();
                        }
                    }                 
                }
            }
            catch(SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.UpdateOrderFeedBackList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
        }
        #endregion

        private double GetProLineWeight(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                double weight = 0;
                double w1 = 0, w2 = 0, w3 = 0;
                string query = string.Format(GET_JCJDETAILINFO_BY_ORDER_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    bool isFirstLine = true;
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        double tmpw1 = double.Parse(reader["Bal1RealCount"].ToString());
                        double tmpw2 = double.Parse(reader["Bal2RealCount"].ToString());
                        double tmpw3 = double.Parse(reader["Bal3RealCount"].ToString());
                        if (isFirstLine && (tmpw1 > 0 || tmpw2 > 0 || tmpw3 > 0))
                        {
                            w1 = tmpw1;
                            w2 = tmpw2;
                            w3 = tmpw3;
                            weight = w1 + w2 + w3;
                            isFirstLine = false;
                        }
                        if (!isFirstLine)
                        {
                            if (w1 < tmpw1 || w2 < tmpw2 || w3 < tmpw3)
                                break;
                            else
                            {
                                w1 = tmpw1;
                                w2 = tmpw2;
                                w3 = tmpw3;
                                if (w1 == 0 && w2 == 0 && w3 == 0)
                                    break;
                            }
                        }
                    }
                    reader.Close();
                }
                weight -= (w1 + w2 + w3);
                return weight;
            }
            catch (SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOrderFeedBackFromJCJ() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return 0;
            }
        }
    }
}
